Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘learning’

A-Z of In-Memory OLTP : Performance Tip for Placement of Data and Delta Files (Checkpoint Files)

Posted by blakhani on April 29, 2014


While preparing my session about In-Memory OLTP for internal audience, I learned something new about placement of checkpoint files. In case you don’t know about checkpoint files, I would like you to read about checkpoint (part 1 and part 2).

Data files and Delta files are created in round robin fashion. Let’s assume that we have two drives W and X and we want to distribute the IO load of In-Memory table. In general, for disk based table, what we used to do? Create the file on each drive? Yes, that would work for disk bases table but there is a little behavior which we should know before doing such thing for in-memory tables.

For demo purpose, I am going to use two folder on same drive to mimic two drives.

USE master
GO
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W'
    ,filename = 'c:\dataHK\ObjectsDemo_W'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X'
    ,filename = 'c:\dataHK\ObjectsDemo_X'
    ) TO filegroup ObjectsDemo_mod
GO

So, I have two folders “ObjectsDemo_W” and “ObjectsDemo_X” to represent W and X drives. Once we create a in-memory table, we should see checkpoint file pairs (CFP) created. 
USE ObjectsDemo
GO

CREATE TABLE dbo.t1 (
    c1 INT NOT NULL PRIMARY KEY NONCLUSTERED
    ,c2 INT
    )
    WITH (memory_optimized = ON)
GO

Let’s have a look at folders now.

image

One folder (Drive W) contains only data files (pre-created at 16 MB each) and another folder (Drive X) contains only delta file (pre-created at 1 MB each). That would not put uniform load on the both folders. The files are placed in this fashion because data and delta files are created in container into round-robin fashion

Here is the tip: Since we have even number of drives, we should create two folders on each drive and place files in 4 containers instead of 2 containers. We need to remember that first two containers should be from same drive.

use master
go
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W1'
    ,filename = 'c:\dataHK\ObjectsDemo_W1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W2'
    ,filename = 'c:\dataHK\ObjectsDemo_W2'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X1'
    ,filename = 'c:\dataHK\ObjectsDemo_X1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X2'
    ,filename = 'c:\dataHK\ObjectsDemo_X2'
    ) TO filegroup ObjectsDemo_mod
GO


W1 and W2 are two container for mimicking two folder on W and same for X as well. Now, if we create a table, due to round robin, we should see below.

image

Now we have DATA file distributed and should have better usage of both driver. What you should do if you have ODD number of drives? Well, nothing because of round robin, data and delta files would be distributed automatically. 

Bottom line: If you have plans to create file stream containers on odd number of drives, create them using trick mentioned above. If you have even number of containers then no special consideration.

Hope you have learned something new today.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , | 2 Comments »

    SQL 2014 Learning Series # 1 – New Permission – Connect any database

    Posted by blakhani on March 27, 2014


    Finally, I was able to get the RTM bits of SQL Server 2014 and installed it on my laptop. Since there are lots of new things, I thought of sharing this with community. I will write them under “SQL 2014 Learning Series”

    First thing first.. Here is the @@version for SQL 2014 on my laptop.

    Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

     

    After installing SQL Server 2014, I wanted to check what’s newly added in security layer of SQL Server. So I compared the permission available under server level and found three new permissions in SQL Server 2014

    image

    My default instance is SQL 2012 and I can see 31 permission whereas SQL 2014 has 34 permissions. These permissions are listed under Server properties >  permissions tab.

    image

    Here is the list

    • CONNECT ANY DATABASE
    • IMPERSONATE ANY LOGIN
    • SELECT ALL USER SECURABLES.

    In this blog, we would see use of “Connect any database” . Let me create a SQL login Balmukund on my SQL Instance. Here is the command I have used

    USE [master]
    GO
    CREATE LOGIN [Balmukund] WITH PASSWORD=N'@Very$tr0ngP@$$w0rd'
    GO
    

     

    Now, If I login with the account and try to expand user database, I will get the error.

    image

    This is what we have seen in earlier version of SQL as well. Unless we add user to the database, it would not be possible to expand database.

    Let’s give new permission to that login.

    use [master]
    GO
    GRANT CONNECT ANY DATABASE TO [Balmukund]
    GO
    
    

    Once permission is given, Balmukund should be able to expand database. But wait.. can he see all objects as well?

    image

    Balmukund is unable to see the objects. This is also mentioned in books online “Does not grant any permission in any database beyond connect”

    You may ask… what’s the use? All we are avoiding is just the error message? Well, there have been ask from customers to provide “Read-Only” or “Auditor” role for SQL Server. Combining this with other new permission can help in achieving that. The cool thing about this permission is that it will also allow logins to connect to databases that would be created in the future.

    In next blog, I will show you demo about other new permissions.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL 2014 Learning Series, SQL Server, SQL Server 2014 | Tagged: , , , , , | 4 Comments »

    A-Z of In-Memory OLTP : Enhancement in Catalog Views

    Posted by blakhani on February 20, 2014


    During my interaction with customers, catalog views and DMV words are used interchangeably. In reality, there is a difference between the two. Till SQL Server 2000, there were system table like sysdatabases, sysobjects etc. SQL 2005 and above the new concept of catalog view was introduced which is replacement of system tables. It is the way to see the catalog metadata of SQL Server. The data showed in the catalog view is physically stored on the disk. On the other hand, DMV is a dynamic management view. It’s similar to sysprocesses which we have used in SQL 2000. As soon as query is fired, the data is read from memory structures and showed in row-column format.

    In this blog, I’ll try to show the changes to existing catalog view to support In-Memory OLTP (code name Hekaton)

    sys.tables

    There are few new columns introduced in sys.tables to get details about In-Memory Tables.

    Column Name Values
    durability  0 = SCHEMA_AND_DATA
    1 = SCHEMA_ONLY
    durability_desc  Description of value in durability Column
    is_memory_optimized  1 – Yes, 0 – No

     

    In my database, I have created two tables; One disk based and one in-memory optimized.

    SELECT name
        ,durability
        ,durability_desc
        ,is_memory_optimized
    FROM sys.tables

    image

     

    sys.table_types

    There is a new column is_memory_optimized is introduced. I would create a type using below T-SQL taken from Books online

    Use SQLSeverHelp_IMO
    go
    CREATE TYPE dbo.SalesOrderDetailsType AS TABLE
    (
           so_id int NOT NULL,
           lineitem_id int NOT NULL,
           product_id int NOT NULL,
           unitprice money NOT NULL,
    
           PRIMARY KEY NONCLUSTERED (so_id,lineitem_id)
    ) WITH (MEMORY_OPTIMIZED=ON)
    GO
    

    Now, look at catalog view.

    select  name, is_memory_optimized 
    from    sys.table_types
    

    image

    sys.indexes

    With the introduction of Hash Indexes, there is new value for type_desc column as shown below.

     SELECT object_name(object_id) TableName
        ,name 'IndexName'
        ,type_desc
    FROM sys.indexes
    WHERE object_id IN (
            SELECT object_id
            FROM sys.tables
            WHERE is_memory_optimized = 1
            )
    
    

    image

    sys.index_columns

    No Change in the columns but it’s important to note that since there is no sorting order in HASH Indexes, the column is_descending_key is shown as 0 which is ignorable.

    sys.data_spaces

    New values available in type column as below.

    Column Name Values
    type  FX = Memory-optimized tables filegroup
    type_desc  MEMORY_OPTIMIZED_DATA_FILEGROUP

     

    use SQLSeverHelp_IMO
    go
    select  * 
    from    sys.data_spaces
    

    image

     

    sys.sql_modules

    A new column uses_native_compilation has been added to identify if it’s a natively compiled procedure. Same is true for sys.all_sql_modules as well. We already have a proc in the database.

    Select object_name(object_id), 
        uses_native_compilation  
    from  sys.sql_modules 
    

    image

     

    In next post, we would explore DMVs related to In-Memory OLTP.

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , | Leave a Comment »