Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,581,868 hits
  • Select GETDATE()

    March 2019
    M T W T F S S
    « Apr    
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Advertisements

Posts Tagged ‘structure’

A-Z of In-Memory OLTP : Monitoring Memory Usage

Posted by blakhani on January 16, 2014


Monitoring is a human nature to be safe and avoid and unexpected failure. Whenever we drive a car at high speed, its human nature to have a look at speedometer. Whenever you go to visit doctor, he would do some monitoring of certain parameters and then ask relevant question to troubleshoot the problem which you tell him. (Oh God! Troubleshooting has become part of my examples, even for doctor!)

In SQL Server, how do you normally monitor space used by a table? Generally we use sp_spaceused and provide table name which gives good amount of details. But interestingly enough, the store procedure would NOT work for in-memory tables as shown below.

select count(*) as 'count' 
from [dbo].[MyFirstMemporyOptimizedTable]
go
sp_spaceused 'MyFirstMemporyOptimizedTable'
go

 

image

Our table MyFirstMemporyOptimizedTable has good amount of rows and can’t be using zero space as reported by sp_spaceused. In order to get the memory used by in-memory tables, we need to use another DMV called dm_db_xtp_table_memory_stats. Now, how do I write a query? I generally look for the UI interface or standard report which gives me the data and then use profiler to find the nicely formatted query.

I was able to find a standard report for memory usage of in-memory tables called “Memory Usage By Memory Optimized Objects” as shown below.

image

I launched the report and captured profiler to get the query. Here is the query which can get us the data for all IMO tables in the database. (captured by profiler, not written by me)

 SELECT t.object_id
    ,t.NAME
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_table_kb) / 1024.00)
            ), 0.00) AS table_used_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb) / 1024.00)
            ), 0.00) AS table_unused_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_indexes_kb) / 1024.00)
            ), 0.00) AS index_used_memory_in_mb
    ,ISNULL((
            SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb) / 1024.00)
            ), 0.00) AS index_unused_memory_in_mb
FROM sys.tables t
INNER JOIN sys.dm_db_xtp_table_memory_stats TMS ON (t.object_id = TMS.object_id)

Here is the output:

image

If we look at report, we would get exactly same output but in more “presentable” format as below.

image

If we look at the bottom area of report, it’s the output of earlier query.

If you find other ways to monitor the memory usage, write it in comment section and share with the world.

  • 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 | Tagged: , , , , , , , , , , , , , , , , | 1 Comment »

    A-Z of In-Memory OLTP : Hash Indexes (Part 2)

    Posted by blakhani on January 14, 2014


    Imagine that you are standing in front of a multistoried apartment and don’t know the apartment where you friend resides (find 1 out of 100). It would be tough to knock each and every door to check if the house belongs to your friend (perform a Scan). What if there is a directory (Index) with the security guard having name and apartment number? Easy? Each of the apartment is a row and you know the predicate (where clause). Indexes in any technology (and real life as well) are used for faster access to the data.

    In the previous post of the A-Z Series, we have seen how a single hash index looks like and learned about hash collision. In this post let’s go one step further understand how two hash indexes on the same table would look like. To make picture more intuitive, I have added color to the column matching with the bucket they belong to. Same as earlier, I am using LEN function as hash function.

    image

    Along with earlier index on fName column, we now have another on Company column. If we try to compare above image with single hash index image in previous blog, it’s clear that now we have “Pointer 2 added” in the row header area of the row (read here). As we can see above, we have three rows falling into same bucket for company column. The bucket of hash index would point to first row of hash bucket <Balmukund, Microsoft>. Due to collision on company Microsoft, there would be chain pointers in the existing row to point to next row of the same bucket (red color arrows in above image)

    In above picture, we have assumed that there is no deletion happened and that’s why we see ∞ (infinity) in End Timestamp for all four rows. This means that all rows are valid for timestamp 300 onwards (begin timestamp is max 300). If delete or update (=delete + insert) happens for a row then as described in earlier blog, the timestamp would be closed for deleted data and new row would be created with new begin timestamp. Lets assume that we fired a update statement to modify Balmukund’s company to Ramco at time stamp 350. We would put end timestamp as 350 for <Balmukund, Microsoft> row and insert new row with <Balmukund , Ramco>. All pointers need modification. Since LEN(Ramco) = 5 and there is no hash collision, new pointer is added.

    image

    Later when garbage collection happens, first row <Balmukund, Microsoft> would be removed and pointer would be modified.

    image

    You may ask – Is there any way to find how many hash buckets we have and do we have collision? Yes, of course! SQL Server has DMV dm_db_xtp_hash_index_stats available to help us investigate. Let’s use the script to understand this concept.

    -- Create database with IMO Filegroup, If exists drop it.
    Use Master
    go
    If db_id('SQLSeverHelp_IMO') is NOT NULL
    drop database SQLSeverHelp_IMO
    GO
    CREATE DATABASE SQLSeverHelp_IMO 
        ON PRIMARY (
        NAME = [SQLSeverHelp_IMO_data]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
        )
        
        ,FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA ( 
        NAME = [SQLSeverHelp_IMO_dir]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
        ) 
        
        LOG ON (
        NAME = [SQLSeverHelp_IMO_log]
        ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'
        ) 
        COLLATE Latin1_General_100_BIN2 
    GO
    -- Create table in database
    -- use the database which is already created
    Use SQLSeverHelp_IMO
    GO
    CREATE TABLE MyFirstMemporyOptimizedTable
    (
        iID INT NOT NULL,
        vFName VARCHAR(20) NOT NULL,
        vLName VARCHAR(20) NOT NULL,
        CONSTRAINT imo_pk_iID primary key NONCLUSTERED HASH (iID) WITH (BUCKET_COUNT = 200),
        index imo_idx_vFname  NONCLUSTERED HASH (vFName) WITH (BUCKET_COUNT = 200)
    ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
    GO
    

     

    I have given bucket count as 200. can you guess how much bucket SQL Sever is going to create? If you can’t answer then you have not read part 1 of hash indexes, which I mentioned in beginning. SQL is going to put 256 buckets for both indexes. Let’s verify!

    Select  name 'Index Name', 
            object_name( object_id) 'Table Name',
            bucket_count 'Number of Buckets'
    from    sys.hash_indexes
    order by 2, 1 asc

    Index Name        Table Name                     Number of Buckets

    —————– —————————— —————–

    imo_idx_vFname    MyFirstMemporyOptimizedTable   256

    imo_pk_iID        MyFirstMemporyOptimizedTable   256

    (2 row(s) affected)

    Let’s insert 90000 rows in the table using natively complied procedure as below.

    Use SQLSeverHelp_IMO 
    GO
    CREATE PROCEDURE [dbo].[InsertName] 
      WITH 
        NATIVE_COMPILATION, 
        SCHEMABINDING, 
        EXECUTE AS OWNER
    AS 
    BEGIN ATOMIC 
      WITH 
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
     LANGUAGE = 'us_english')
     
    DECLARE @counter INT 
    SET @counter = 1
    
    WHILE @counter <= 90000 
      BEGIN 
          INSERT INTO dbo.MyFirstMemporyOptimizedTable 
          VALUES      (@counter, 
                       'SQLServer-Help.com', 
                       'Balmukund Lakhani');
          INSERT INTO dbo.MyFirstMemporyOptimizedTable 
          VALUES      (@counter + 1, 
                       'ExtremeExperts.com', 
                       'Vinod Kumar M');
          INSERT INTO dbo.MyFirstMemporyOptimizedTable 
          VALUES      (@counter + 2, 
                       'Blogs.msdn.com', 
                       'Balmukund Lakhani') 
    
          SET @counter = @counter + 3 
      END
    END
    GO
    
    exec InsertName
    go
    
    

     

    Now, let’s examine hash index statistics.

    SELECT Object_name(his.object_id)        'Table Name', 
           idx.name                            'Index Name', 
           total_bucket_count                'total buckets', 
           empty_bucket_count                'empty buckets',            
           total_bucket_count - empty_bucket_count as 'used buckets', 
           avg_chain_length                    'avg chain length', 
           max_chain_length                    'max chain length', 
           90000                                   as 'Rows - hardcoded value' 
    FROM   sys.dm_db_xtp_hash_index_stats as his 
           JOIN sys.indexes as idx 
             ON his.object_id = idx.object_id 
                AND his.index_id = idx.index_id;   
    

    Table Name                      Index Name       total buckets  empty buckets  used buckets  avg chain length  max chain length Rows – hardcoded value

    ——————————- —————- ————– ————– ————- —————– —————  ———————-

    MyFirstMemporyOptimizedTable    imo_idx_vFname   256            253            3             30000             30000            90000

    MyFirstMemporyOptimizedTable    imo_pk_iID       256            0              256           351               355              90000

    (2 row(s) affected)

    image

    Chain length in conjunction with buckets in the output tells that for index imo_idx_vFname, we have only three bucket and each bucket has 30000 entries. If we go back and examine the stored procedure, we are inserting only three values in loop of 30000 for vFName column. Whereas for the other index imo_pk_iID, we don’t have any free bucket and chain length is more for each bucket. This is the right candidate for more number of buckets.  Typical value of bucket count is between 1 to 2 times of distinct values on that column. Remember that bucket count can’t be change on the fly – whole table needs to be dropped and recreated.

    Books online references:sys.dm_db_xtp_hash_index_stats and Determining the Correct Bucket Count for Hash Indexes

    Hope you have already downloaded SQL 2014 CTP2 and following this series with me!

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

    A-Z of In-Memory OLTP : Hash Indexes (Part 1)

    Posted by blakhani on January 9, 2014


    Let’s imagine you came to meet my friend Vinod (b|t) and asked him to find “Readable Secondary” word in our book on AlwaysOn of 420 pages. How would he do it? Do you think he would start reading each and every page to find that word? No! He knows that there is a Index page at the end of the book which will tell him the word and the page number where its located. Our search becomes easier with the help of Index, correct? In the same way, SQL Server also uses indexes to find a row which you are asking to select/update/delete.

    In my previous blog about T-SQL constructs, we have discussed that index on in-memory need to be created during table creation itself. Alter of in-memory table is not allowed. Here are the few point we should be aware.

    • Indexes on in-memory tables reside only in memory. Only index definition is stored on disk and index structure is created while starting of the database. Refer this blog.
    • At least one index is needed. For SCHEMA_AND_DATA durability a primary key needs to be created.

    Msg 41321, Level 16, State 7, Line 21
    The memory optimized table ‘TableName’ with DURABILITY=SCHEMA_AND_DATA must have a primary key.
    Msg 1750, Level 16, State 0, Line 21
    Could not create constraint or index. See previous errors.

    • If we use SCHEMA_ONLY as durability then here is the error message if there is no index declared.

    Msg 41327, Level 16, State 7, Line 21
    The memory optimized table ‘TableName’ must have at least one index or a primary key.
    Msg 1750, Level 16, State 0, Line 21
    Could not create constraint or index. See previous errors.

    • There is no concept of clustered index on in-memory table. Only non-clustered indexes are supported.
    • Total 8 Indexes are supported on in-memory tables. Exceeding this would cause below error message

    Msg 1910, Level 16, State 1, Line 14
    Could not create in-memory index ‘IndexName’ because it exceeds the maximum of 8 allowed per table or view.
    Msg 1750, Level 16, State 0, Line 14
    Could not create constraint or index. See previous errors.

    • String column on which index is created must use *_BIN2 collation else we would encounter below error.

    Msg 12328, Level 16, State 106, Line 14
    Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.
    Msg 1750, Level 16, State 0, Line 14
    Could not create constraint or index. See previous errors.

    Here is the sample failure and the solution for error 12328:

    CREATE TABLE Failure (
        [iID] [int] NOT NULL,
        [vName] [nvarchar](20) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1024),
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
    GO
    
    /*
    Msg 12328, Level 16, State 106, Line 14
    Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.
    Msg 1750, Level 16, State 0, Line 14
    Could not create constraint or index. See previous errors.
    */
    
    CREATE TABLE Success (
        [iID] [int] NOT NULL,
        [vName] [nvarchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1024),
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
    GO
    /*
    Command(s) completed successfully.
    */

    Another option would be to specify the collation during database creation itself. Using this, we need not specify different collation during table creation as column’s default collation would be database collation (highlighted in below script)

    CREATE DATABASE SQLSeverHelp_IMO 
        ON PRIMARY (
        NAME = [SQLSeverHelp_IMO_data]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
        )
        
        ,FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA ( 
        NAME = [SQLSeverHelp_IMO_dir]
        ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
        ) 
        
        LOG ON (
        NAME = [SQLSeverHelp_IMO_log]
        ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'
        ) 
    COLLATE Latin1_General_100_BIN2
    GO
    use SQLSeverHelp_IMO
    go
    CREATE TABLE Failure(
        [iID] [int] NOT NULL,
        [vName] [nvarchar](20) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1000),
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
    GO
    

     

    Now, let’s understand the type of indexes which can be created on in-memory tables. There are two kind of Indexes, Hash Indexes and Range Indexes. In this blog, I would explain hash indexes. Since there is a lot of information, I will continue about hash indexes in next post as well.

    At this point, it is important that you know the row structure of in-memory tables. I have already blogged about it in my previous post here.

    HASH INDEX

    Hash index is a list of pointers and each of them is called as bucket. (Wikipedia) The number of buckets needs to be specified while creating the index. It is important to note that the number of hash buckets can’t be changed once table is created. There is no alter operation possible so we would end up in dropping and recreating the table for re-declaring the bucket count. Let’s assume for simplicity that I have created index with 4 buckets. I will start with very simple example with 4 has buckets with three rows and only one hash index.

    image

    For simplicity purpose, I have assumed the hash function as length of the column and it’s created on FName column (Note that the real hash function used in SQL is much more random and unpredictable, but to illustrate I am using Length function) Len(Vinod) = 5 so it would fall into first bucket and there would be a pointer which is pointing to actual row (middle row in image). In the same way (length), PinalD would be in hash bucket 2 (hashed values = 6) and Balmukund would be in hash bucket 3 (hashed values = 9)

    Now, let’s assume that I insert another value SQLServer into Name column. Which bucket it would be? Since len(SQLServer) = 9 it would fall into bucket # 3 but we already have a row sitting there. This is the situation which is called as “Hash Collision” In this case, SQL Server would add a pointer in the existing row to point to next row as shown below.

    image

    Now imagine a situation that we have many rows (may be 10000) and have less buckets (only 4) then there would be a lots of hash collisions and length of chain would keep on increasing for each bucket. This would cause performance degradation while search for data in a bucket. Let’s understand a small concept about BUCKET_COUNT parameter in SQL Server.

    BUCKET_COUNT

    Number of bucket for a hash index is defined using BUCKET_COUNT parameter during index creation. SQL Server creates number of hash buckets as always power of 2. If we declare number of bucket which is not a power of 2 then it would be automatically rounded-off to next number which is exact power of 2. In my example above, I have given BUCKET_COUNT as 1000. Doing a little math, we can easily find that next valid value would be 1024 (2^10). Let’s have a look at demo to understand this better.

    In below script, I would create 65 tables with there name as MemporyOptimizedTable<n> where n is a integer between 1 to 65 and also denotes number of buckets for each table. (MemporyOptimizedTable1 has 1 bucket, MemporyOptimizedTable2 has 2 buckets and so on…)

    -- use the database which is already created
    use SQLSeverHelp_IMO
    go
    -- declare the looping variables
    declare @loopCounter int , @CreateString nvarchar(4000)
    -- initialize
    Select @loopCounter = 1, @CreateString = ''
    
    while @loopCounter <= 65 -- loop 65 times
    begin
        -- build dynamic string with table name and bucket count having loop counter
        set @CreateString = '
    
        CREATE TABLE MemporyOptimizedTable'+ CONVERT(varchar(10), @loopCounter)+'
        (
        iID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = '+CONVERT(varchar(10), @loopCounter) +'),
        vFName VARCHAR(20) NOT NULL,
        vLName VARCHAR(20) NOT NULL
        ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)'
    
        -- execute dynamic query
        Exec (@CreateString)
        -- increment the counter
        set @loopCounter = @loopCounter+1
    end
    

     

    Now, let’s look at the bucket_count which SQL has created using catalog view sys.hash_indexes

    Select    object_name( object_id) 'Table Name',
            bucket_count            'Number of Buckets'
    from    sys.hash_indexes
    order    by 2, 1 asc
    

     

    image

    As we can see above that even if we have declared Bucket_count as 9, SQL Server has rounded off to 16. Here is the simple script which can be used to find the number of buckets for a given input value.

    DECLARE @inputNumber int = 65 ;    -- BUCKET_COUNT during index creation
    
    DECLARE @BucketsCreatedBySQL int = 0;
    -- Check of @inputNumber is power of 2. if yes, that's the bucket number
    
    IF (LOG(@inputNumber,2) = ROUND(LOG(@inputNumber,2),0))    
        SELECT @BucketsCreatedBySQL = @inputNumber;
    ELSE
    -- If not power of 2, round off to next power of 2
        SELECT @BucketsCreatedBySQL = POWER( 2, floor(LOG(@inputNumber,2))+1); 
    SELECT @BucketsCreatedBySQL [BucketsCreatedBySQL]
    
    
    

    image

    This blog has become longer than I expected.. There is a lot more to cover about hash index I would continue discussion about hash indexes in next blog.

  • 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: , , , , , , , , , , , , , , | 5 Comments »