Help: SQL Server

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)

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

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.

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

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.

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!