Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘inmemory’

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
  • Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , | 2 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 »

    A-Z of In-Memory OLTP : Extended Events (XEvents)

    Posted by blakhani on February 18, 2014


    All of us want to know “internals” of many things. Doctors want to know internals of body; Engineer wants to know internals of machines. On the same line SQL DBA always wants to know internals of SQL Server. Sometimes there are easier ways to look at internals of SQL Server via extended events as compare to any other tool available. In this blog, we would have a look at the XEvents (eXtended Events) which are available to provide information about In-Memory OLTP. If you want to learn about XEvents, then start from Jonathan’s blog series (An XEvent a Day)

    To get the list of XEvents which are related to In-Memory OLTP, we can either use query or Interface. There are three packages available for XTP (Hope you have read my initial blog of series which explains XTP, if not, read here)

    Name Description
    XtpRuntime Extended events for the XTP Runtime
    XtpCompile Extended events for the XTP Compile
    XtpEngine Extended events for the XTP Engine

     

    We can view events for Registered Packages using below queries. I have modified the one available on books online. Here is the query and the output

    USE msdb 
    
    SELECT p.name, 
           c.event, 
           k.keyword, 
           c.channel, 
           c.description 
    FROM   (SELECT event_package=o.package_guid, 
                   o.description, 
                   event=c.object_name, 
                   channel=v.map_value 
            FROM   sys.dm_xe_objects o 
                   LEFT JOIN sys.dm_xe_object_columns c 
                          ON o.name = c.object_name 
                   INNER JOIN sys.dm_xe_map_values v 
                           ON c.type_name = v.name 
                              AND c.column_value = Cast(v.map_key AS NVARCHAR) 
            WHERE  object_type = 'event' 
                   AND ( c.name = 'CHANNEL' 
                          OR c.name IS NULL )) c 
           LEFT JOIN (SELECT event_package=c.object_package_guid, 
                             event=c.object_name, 
                             keyword=v.map_value 
                      FROM   sys.dm_xe_object_columns c 
                             INNER JOIN sys.dm_xe_map_values v 
                                     ON c.type_name = v.name 
                                        AND c.column_value = v.map_key 
                                        AND 
                             c.type_package_guid = v.object_package_guid 
                             INNER JOIN sys.dm_xe_objects o 
                                     ON o.name = c.object_name 
                                        AND o.package_guid = c.object_package_guid 
                      WHERE  object_type = 'event' 
                             AND c.name = 'KEYWORD') k 
                  ON k.event_package = c.event_package 
                     AND ( k.event = c.event 
                            OR k.event IS NULL ) 
           INNER JOIN sys.dm_xe_packages p 
                   ON p.guid = c.event_package 
    WHERE  name LIKE '%xtp%' 
            OR c.event LIKE '%xtp%' 
            OR c.description LIKE '%xtp%' 
            OR k.event LIKE '%xtp%' 
            OR k.keyword LIKE '%xtp%' 
    ORDER  BY 1, 
              keyword DESC, 
              channel, 
              event  

    Here is the output

    Name Event keyword channel description
    sqlserver natively_compiled_proc_slow_parameter_passing xtp Analytic Occurs when a Hekaton procedure call dispatch goes to slow parameter passing code path
    sqlserver after_natively_compiled_proc_entry_removal_on_drop xtp Debug Fired after the procedure cache entry is flushed when dropping a natively compiled procedure.
    sqlserver before_natively_compiled_proc_entry_removal_on_drop xtp Debug Fired before the procedure cache entry is flushed when dropping a natively compiled procedure.
    sqlserver natively_compiled_proc_execution_started xtp Debug Fired before a natively compiled procedure is executed.
    sqlserver xtp_create_procedure xtp Debug Occurs at start of XTP procedure creation.
    sqlserver xtp_create_table xtp Debug Occurs at start of XTP table creation.
    sqlserver xtp_deploy_done xtp Debug Occurs at completion of XTP object deployment.
    sqlserver xtp_matgen xtp Debug Occurs at start of MAT generation.
    sqlserver xtp_offline_checkpoint_scan_start xtp Debug Fired by XTP offline checkpoint when the checkpoint thread begins.
    sqlserver xtp_offline_checkpoint_scan_stop xtp Debug Fired by XTP offline checkpoint when the checkpoint thread stops.
    sqlserver xtp_recover_done xtp Debug Occurs at completion of log recovery of XTP table.
    sqlserver xtp_recover_table xtp Debug Occurs at start of log recovery of XTP table.
    sqlserver xtp_storage_table_create xtp Debug Occurs at just before the XTP storage table is created.
    XtpCompile cgen deploy Debug Occurs at start of C code generation.
    XtpCompile invoke_cl deploy Debug Occurs prior to the invocation of the C compiler.
    XtpCompile mat_export deploy Debug Occurs at start of MAT export.
    XtpCompile pit_export deploy Debug Occurs at start of PIT export.
    XtpCompile pitgen_procs deploy Debug Occurs at start of PIT generation for procedures.
    XtpCompile pitgen_tables deploy Debug Occurs at start of PIT generation for tables.
    XtpEngine after_changestatetx_event transaction Debug Fires after transaction changes state
    XtpEngine alloctx_event transaction Debug  
    XtpEngine attempt_committx_event transaction Debug Is raised when a transaction is asked to commit
    XtpEngine before_changestatetx_event transaction Debug Fires before transaction changes state
    XtpEngine dependency_acquiredtx_event transaction Debug Raised after transaction takes a dependency on another transaction
    XtpEngine endts_acquiredtx_event transaction Debug Fires after transaction acquires an end timestamp
    XtpEngine redo_single_hk_record transaction Debug Redo on a HK log record
    XtpEngine waiting_for_dependenciestx_event transaction Debug Raised when we have explicitly waited for dependencies to clear
    XtpEngine xtp_create_log_record transaction Debug Fires when the XTP engine creates a log record.
    XtpEngine gc_base_generation_evaluation gc Debug Indicates that an evaluation of updating the GC base generation has been made.
    XtpEngine gc_base_generation_updated gc Debug Indicates that the oldest active transaction hint used for calculating the GC base generation has been updated.
    XtpEngine gc_cycle_completed gc Debug Indicates that a GC notification has been enqueued.
    XtpEngine gc_notification gc Debug Indicates that a GC notification has not being able to process a notification.
    XtpEngine xtp_checkpoint_file_flush checkpoint Debug Indicates the point at which a given file has been flushed to disk.
    XtpEngine xtp_checkpoint_file_flush_complete checkpoint Debug Indicates the point at which all in-flight buffers have been flushed to disk.
    XtpEngine xtp_checkpoint_file_read checkpoint Debug Indicates reading of a file in XTP checkpoint recovery.
    XtpEngine xtp_checkpoint_write_io checkpoint Debug Indicates that the checkpointing subsystem has issued or completed a write IO.
    XtpEngine xtp_root_deserialized checkpoint Debug Indicates that the load of a checkpoint root is complete.
    XtpEngine xtp_root_serialized checkpoint Debug Indicates that the write of the checkpoint root is complete.
    XtpRuntime bind_md deploy Debug Occurs prior to binding metadata for a memory optimized table.
    XtpRuntime bind_tables deploy Debug Occurs prior to binding tables for a natively compiled procedure.
    XtpRuntime create_table deploy Debug Occurs prior to creating memory optimized table.
    XtpRuntime deserialize_md deploy Debug Occurs prior to deserializing metadata.
    XtpRuntime load_dll deploy Debug Occurs prior to loading the generated DLL.
    XtpRuntime recover_done deploy Debug Occurs at completion of checkpoint recovery of a memory optimized table.
    XtpRuntime recover_table deploy Debug Occurs at start of checkpoint recovery of a memory optimized table.
    XtpRuntime serialize_md deploy Debug Occurs prior to serializing metadata.

     

    This output shows something interesting. Notice that most of the events are under “Debug” Channel. This means, if we would go to wizard of creation of XEvent session in SSMS, we would not see them because Debug channel is not checked by default.

    image

    After checking Debug, we should be able to see the events. In below, I have put filter of work XTP.

    image

    Let’s use XEvent to understand a concept. We have learned that DLLs of table is loaded as soon as database is started whereas the stored procedure DLL is loaded when its first executed. We have already discussed this concepts in Behind the Scenes

    To demonstrate, I have created below XEvent Session. My database already has table and natively compiled stored procedure.

     CREATE EVENT SESSION [SQLServerHelp] ON SERVER 
    
         ADD EVENT sqlserver.database_started 
            (ACTION(sqlos.cpu_id, sqlserver.database_id) 
                WHERE ([database_id] = (5)))
        ,ADD EVENT XtpRuntime.load_dll 
            (ACTION(sqlos.cpu_id, sqlserver.client_hostname)) 
            
        ADD TARGET package0.event_file (SET filename = N'SQLServerHelp')
        
        WITH (
                MAX_MEMORY = 4096 KB
                ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
                ,MAX_DISPATCH_LATENCY = 30 SECONDS
                ,MAX_EVENT_SIZE = 0 KB
                ,MEMORY_PARTITION_MODE = NONE
                ,TRACK_CAUSALITY = OFF
                ,STARTUP_STATE = ON
                )
    GO
    

     

    I have put filter on database_id = 5 which you may want to change based on your system. After creating the session, I restarted SQL Server and after some time I executed stored procedure as well. As we can see in below that table DLL is loaded before database startup was complete.

    image

    Also notice that stored procedure DLL was loaded when I have manually executed stored procedure. Wow! extended events have confirmed our learning about DLLs generation and loading.

    Conclusion

    There are various extended events which can help us understand internals of hekaton / In-Memory OLTP. We will try to use more and see practical of theory which we have learned so far in this A-Z Series.

    Hope you have learned something new today!

  • 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 »