SQL Server – Extended Events – Internal Session Definitions

Background

Wanted to track Extended Events, but ran into a stumbling block.

And, we go…

 

Metadata

Extended Events

Active Sessions ( sys.dm_xe_sessions )

Code


select 
		  [sessionName] = tblXES.[name]
		, [sessionCreateTime] = tblXES.[create_time]
		, [sessionSource] = tblXES.[session_source]
		--  tblXES.*

from   sys.dm_xe_sessions tblXES

Output

Explanation

  1. session_source
    • We can group Extended Event based on Source
      • Source :-
        • server
        • internal
    • In our case
      • server
        • system_health
          • MSFT Defined
        • performance
          • Defined by us
      • internal
        • hkenginexesession
          • Added in SQL Server v2016
          • Tracks Hekaton ( “In-Memory Database” Events )
        • sp_server_diagnostics session
          • Added in SQL Server v2014
          • SQL Server Diagnostic Events

 

Extended Event Definitions ( sys.server_event_sessions )

Code


select 
		  [sessionID] = tblSES.[event_session_id]

		, [extendedEvent]
			 = tblSES.[name]

		, [eventRetentionMode] 
			= tblSES.[event_retention_mode]

		, [trackCasualty]
			 = tblSES.[track_causality]

		, [startupState] 
			= tblSES.[startup_state]

		--, tblSES.*

from   sys.server_event_sessions tblSES

Output

Explanation

  1. sessionID
    • event_session_id
      • Starts at 65534
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • track_causality
  5. Startup Start
    • startup_state

Tie things together

Let us join our two sources,  sys.server_event_sessions & sys.dm_xe_sessions.

 

Inner Join – sys.server_event_sessions & sys.dm_xe_sessions

Code

select 
		  [sessionID] =  tblSES.[event_session_id]
		, [extendedEvent] = tblSES.[name]
		, [eventRetentionMode] = tblSES.[event_retention_mode]
		, [trackCausality ] = tblSES.[track_causality]
		, [startupState] = tblSES.[startup_state]

		 , [sessionCreateTime] = tblXES.[create_time]
		 , [sessionSource] = tblXES.[session_source]

from  sys.server_event_sessions tblSES

left outer join sys.dm_xe_sessions tblXES

        on tblSES.[name]  = tblXES.[name]

Output

Explanation

  1. sessionID
    • event_session_id
  2. Extended Event
    • name
  3. Event Retention Mode
    • Column :- event_retention_mode
  4. Track Casualty
    • Column :- track_causality
  5. Startup State
    • Column :- startup_state
  6. sessionCreateTime
    • Column :- sys.dm_xe_sessions.create_time
  7. sessionSource
    • Column :- sys.dm_xe_sessions.session_source

Workaround ( using cte ) – sys.server_event_sessions & sys.dm_xe_sessions

Code

; with cteExtendedEvent
(
      [name]
    , [id]
    , [sessionSource]
)
as
(

    /*
        Get Defined Event Sessions
    */ 
    select 
              tblSES.[name]

            , [sessionID]
                = tblSES.[event_session_id]

            , [sessionSource]
                = null


    from   sys.server_event_sessions tblSES

    union
    
    /*
        Get Running Event Sessions that are shown in  sys.server_event_sessions
    */ 

    select 
              tblXES.[name]

            , cast(null as int)

            , [sessionSource]
                = tblXES.[session_source]

    from   sys.dm_xe_sessions tblXES

    where  tblXES.[name] not in
                (
                    select [name]
                    from   sys.server_event_sessions tblSES
                )


)
select 

          [extendedEvent] 
            = cteEE.[name]

        , [sessionID] 
             =  tblSES.[event_session_id]


        , [eventRetentionMode]
             = tblSES.[event_retention_mode]

        , [trackCausality] 
            = tblSES.[track_causality]

        , [startupState] 
            = tblSES.[startup_state]

        , [sessionCreateTime]
             = tblXES.[create_time]

        , [sessionSource]
             = tblXES.[session_source]


from  cteExtendedEvent cteEE

left outer join sys.server_event_sessions tblSES

        on cteEE.[name] = tblSES.[name] 

left outer join sys.dm_xe_sessions tblXES

        on cteEE.[name]  = tblXES.[name]

Output

Summary

An astute mind will see that there are some events listed in sys.dm_xe_sessions, but not in sys.server_event_sessions.

Again sys.dm_xe_sessions tracks running Event Sessions.  Whereas sys.server_event_sessions tracks sessions definitions.

Also, keep in mind that sys.dm_xe_sessions is a dynamic management view.  And, sys.server_event_sessions is a system catalog view.

It appears that sys.server_event_sessions has chosen not to expose internal Extended events within the sys.server_event_sessions view.

MSFT choice makes some sense as sys.server_event_sessions by definition exposes definitions.

And, MSFT is likely saying these definitions are private and personal to us and we have chosen not to expose them.

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views/
      • sys.server_event_sessions ( Transact-SQL )
      • sys.dm_xe_sessions ( Transact-SQL )

SQL Server – MEMORY_OPTIMIZED_DATA filegroup – Auto Close?

Background

There is just so much that is not apparent until we trip over it.

Here I am trying to see if I am able to restore MSFT’s sample database, WideWorldImporters, on a SQL Server Express instance.

Btw,  the targeted edition is v2017.

Restore worked flawlessly.

Database Properties

AUTO CLOSE

As I am only going to occasionally use the sampler database on the targeted instance, I wanted to mark it AUTO CLOSE.

My hope is that it will be silent and not use resources while not in use.

Code

Here is the code for setting a database to AUTO CLOSE.


USE [master]
GO

ALTER DATABASE [WideWorldImportersFull]
	 SET AUTO_CLOSE ON 
	 WITH NO_WAIT
GO

Output

Nice error message.

Textual

Msg 10794, Level 16, State 125, Line 4
The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.


Image

 

Summary

Yea, restore worked.

But, outwitted myself, trying to set it to AUTO-CLOSE.

SQL Server – Unable to shrink Transaction Log file

Background

Received an alert stating that we have gotten below our threshold of 10% on one of DB Servers.

Thanks goodness no late night phone calls.

Only saw it coming to work this morning.

The good and bad about smartphone.

TroubleShooting

Windows Desktop

Folder

Image

Explanation

  1. ppsivr_8.ldf is 28 GB

SQL Metadata

Outline

Here are dynamic management views ( DMVs) that we will access…

  1. dbcc sqlperf(logspace)
  2. sys.database_files
  3. sys.databases
  4. sys.dm_exec_requests & sysprocesses

 

Review Transaction Log File Sizes

SQL


dbcc sqlperf(logspace)

Output

 

Explanation

  1. ppsivr
    • Log Size MB
      • 27,699 MB
      • 27 GB

Review Database File Sizes

SQL


-- replace with targeted database
use [database]

SELECT 
		  [name]

		, [type]
			= tblSDF.[type_desc]

		, [filename]
			= tblSDF.[physical_name]

		, [allocatedMB]
			= tblSDF.size / 128

		, [usedMB]
			= CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)
				/ 128

		, [availableSpaceMB]
				 = ( size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) )
					/ 128

FROM sys.database_files tblSDF

order by
		tblSDF.size desc
;

Output

Review Database Transaction Log Re-usability State

SQL


declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		    tblSD.[database_id]
		  , tblSD.[name]
		  , tblSD.[log_reuse_wait_desc]

from   sys.databases tblSD

where  (
			   (@dbid is null)

			or ( tblSD.[database_id] = @dbid)
		
	   )

Output

Explanation

  1. log_reuse_wait_desc
    • The database is not re-cycling its transaction log due to an ‘Active Transaction’

Review Current DB Requests

Code



declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		  tblSDER.[session_id]

		, [database] 
			= db_name(tblSDER.[database_id])

		, [openTransaction]
			= tblSP.[open_tran]

		, tblSDER.[status]

		, tblSDER.[command]

		, tblSDER.[percent_complete]

		, tblSDER.wait_type

		, tblSDER.last_wait_type

		, tblSDER.[reads]

		, tblSDER.[writes]

from   sys.dm_exec_requests tblSDER

inner join master..sysprocesses tblSP

	on tblSDER.[session_id] = tblSP.[spid]

where  (

	         ( tblSDER.[open_tran] != 0 )

	      or ( tblSDER.[database_id] = @dbid)

      )



Output

Explanation

  1. User Transactions
    • It does not appear that we have user transactions that are current using our targeted database
  2. Background
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG
      • It does does not apply that WAIT_XTP_OFFLINE_CKPT_NEW_LOG updates the percent_complete column as so it is a bit difficult to track its current progress

 

Remediation

Here are the things we tried:

  1. dbcc shrinkfile
  2. Take database offline and bring back online

Attempt to Shrink Transaction Log Files

SQL


use [ppsivr]
go

dbcc shrinkfile('ppsivr_log')
go

Output

Explanation

  1. We want to take a good look at Current Size and Used Pages
    • In our case they are same
    • What is in our Transaction Log File?

 

Take Database Offline and bring it back online

Force termination of ongoing sessions

SQL


use master
go

ALTER DATABASE [ppsivr]
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	;

alter database [ppsivr] set offline;

alter database [ppsivr] set online;

ALTER DATABASE [ppsivr]
	SET MULTI_USER WITH ROLLBACK IMMEDIATE
	;

 

Summary

But, nothing worked!

We will come back and talk about what worked.

References

  1. Microsoft
    • Support.Microsoft.com
      • FIX: Offline checkpoint thread shuts down without providing detailed exception information in SQL Server 2014
        Link

Transact SQL – In Memory Programmable Objects

Background

On a couple of SQL Server Instances that was recently grandfathered in to our group, there are a few In-Memory tables.

And, so I find myself using these precious hours in between Christmas and the forthcoming New Year to catch up on In-Memory Tables.

What good are In-Memory tables, without In-Memory Programmable Objects such as Stored Procedures.

Let us quickly create a couple of Stored Procedures.

 

Code

Programmable Objects

Stored Procedures

dbo.usp_Emp_Get_FilteredOnSSN

 




if object_id('[dbo].[usp_Emp_Get_FilteredOnSSN]') is not null
begin
	drop procedure [dbo].[usp_Emp_Get_FilteredOnSSN]
end
go

create procedure [dbo].[usp_Emp_Get_FilteredOnSSN]
(
	@ssn char(9)
)  
with 
		  native_compilation
		, schemabinding
		, execute as owner  
as  
begin atomic  

	with 
	(
		  transaction isolation level=snapshot
		, language=N'us_english'
	)  

	select
			  tblEmp.[oe_emp_ssn]
			, tblEmp.[oe_emp_location]

	from   [dbo].[oe_emp] tblEmp

	where  tblEmp.[oe_emp_ssn] = @ssn


end  
go  

dbo.usp_Emp_Set_FilteredOnSSN

Code


if object_id('[dbo].[usp_Emp_Set_FilteredOnSSN]') is not null
begin
	drop procedure [dbo].[usp_Emp_Set_FilteredOnSSN]
end
go

create procedure [dbo].[usp_Emp_Set_FilteredOnSSN]
(
	  @ssn      char(9)
	, @location char(2)
)  
with 
		  native_compilation
		, schemabinding
		, execute as owner  
as  
begin atomic  

	with 
	(
		  transaction isolation level=snapshot
		, language=N'us_english'
	)  

	update [dbo].[oe_emp]

	set	[oe_emp_location] = @location
		
	where  [oe_emp_ssn] = @ssn

end  
go  


Unit Test

set nocount on;
set XACT_ABORT on;
go

declare @ssn char(9)
declare @location char(2)
declare @location_Temp char(2)
declare @location_v2 char(2)


begin tran

	select top 1
				@ssn = [oe_emp_ssn]
			, @location = [oe_emp_location]
	from   [dbo].[oe_emp] with (SNAPSHOT)

	set @location_Temp = 'NA'

	exec [dbo].[usp_Emp_Set_FilteredOnSSN]
			@ssn = @ssn
		, @location = @location_Temp


	select @location_v2 = [oe_emp_location]
	from   [dbo].[oe_emp] with (SNAPSHOT)
	where  [oe_emp_ssn] = @ssn


	select 
			  [@location] = @location
			, [@location_Temp] = @location_Temp
			, [@location_v2] = @location_v2	

rollback tran

 

Restrictions

There are a little restrictions placed on Stored Procedures marked for Native Compilation.

The restrictions includes

  1. SQL Syntax
    • Can not use like Statement
      • If we attempt to do so, we will get the error pasted below
        • Sample SQL Statement
          • select tblEmp.[oe_emp_ssn],  tblEmp.[oe_emp_location] from [dbo].[oe_emp] tblEmp where tblEmp.[oe_emp_ssn] like @ssn
        • Error Message
          • Msg 10794, Level 16, State 62, Procedure usp_Emp_Get_FilteredOnSSN, Line 42
            The operator ‘LIKE’ is not supported with natively compiled stored procedures.
    • Can not use wildcard (*) for Column Names
      • If we attempt to do so, we will get the error pasted below
        • Sample SQL Statement
          • select tblEmp.* from [dbo].[oe_emp] tblEmp where tblEmp.[oe_emp_ssn] like @ssn
        • Error Message
          • Msg 1054, Level 15, State 2, Procedure
            Syntax ‘*’ is not allowed in schema-bound objects.
    • Can not use Begin Transaction / Rollback-Commit within Native Compiled Stored Procedures
      • The same effect is handled declaratively, as Native Compile SP are designated as atomic
        • Database Features > In-Memory OLTP (In-Memory Optimization)  > Natively Compiled Stored Procedures
          Link

          • BEGIN ATOMIC is part of the ANSI SQL standard.
          • SQL Server supports atomic blocks at the top-level of natively compiled stored procedures, as well as for natively compiled, scalar user-defined functions.
            • Every natively compiled stored procedure contains exactly one block of Transact-SQL statements. This is an ATOMIC block.
            • Non-native, interpreted Transact-SQL stored procedures and ad hoc batches do not support atomic blocks.
        • Sample Code
          • begin atomic
            —– source code —
            end

 

Table of Errors

Msg Error Number Error Details Remediation
 1054 Syntax ‘*’ is not allowed in schema-bound objects.  Explicitly list the Column Names
 10794  The operator ‘LIKE’ is not supported with natively compiled stored procedures.  The Like Operator can not be used in natively compile Stored Procedure.
Please use regular Stored Procedure
41320 EXECUTE AS clause is required, and EXECUTE AS CALLER is not supported, with natively compiled stored procedures  Replace “with native_compilation, schemabinding” with “with native_compilation, schemabinding, , execute as owner

 

 

References

  1. Database Features > In-Memory OLTP (In-Memory Optimization) > Natively Compiled Stored Procedures > Atomic Blocks in Native Procedures
    Link
  2. Database Engine Features and Tasks > Database Features > In-Memory OLTP (In-Memory Optimization) > Scalar User-Defined Functions for In-Memory OLTP
    Link

Transact SQL – Atomicity and In-Memory Tables

 

Background

In this post, we touch on a couple of errors that one might encounter when trying to access In-Memory tables within a Transaction.

Error

Error Number Error Description
 Msg 41333 The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
 Msg 41332 Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

 

 

Code

Here is how to reproduce the errors listed above.

Set Isolation Level

Set Isolation Level – Serializable

Code


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

begin tran

	select top 10 tblE.*

	from   [dbo].[oe_emp] tblE

rollback tran

 

Output – Textual


Msg 41333, Level 16, State 1, Line 20
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.


Output – Image

msg41333-setisolationlevelserializable

 

Explanation

  1. The error message states that the “The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.
  2. It seems that we have to be in Snapshot isolation when we access Memory Optimized Tables

 

Set Isolation Level

Set Isolation Level – Snapshot

Code

 


set nocount on;
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

begin tran

	begin try

		select top 10 tblE.*

		from   [dbo].[oe_emp] tblE

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran

 


Exception
=========
ErrorNumber    :- 41332
ErrorState     :- 0
ErrorProcedure :- -1
ErrorLine      :- 10
ErrorMessage   :- Memory optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.


Output – Image

msg41333-setisolationlevelsnapshot

 

Explanation

  1. So even we set our Transactions Isolation Level to SNAPSHOT, we are still getting an error
  2. Thankfully, a more specific error that states that “Memory Optimized tables and natively compiled stored procedures cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT.

 

 

Remediation

Query

Add Table Hint SNAPSHOT To In-Memory Table Reference

Objective

We added SNAPSHOT as a table hint when referencing In-Memory Tables

Code



set nocount on;

/*
	Add Table Hint, SNAPSHOT, to In-Memory Table References
*/
begin tran

	begin try

		
		select top 10 tblE.*

		from   [dbo].[oe_emp] tblE with (SNAPSHOT)

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran


Explanation

  1. Add Table Hint (SNAPSHOT) to all In-Memory Table References

 

 

Add Table Hints SNAPSHOT To all In-Memory Table Reference

Objective

We added SNAPSHOT as a table hint to all our In-Memory Table

Code


set nocount on;

/*
	Add Table Hint to all In-Memory Table Object References with (SNAPSHOT)
*/
begin tran

	begin try

		
		select top 10 tblOEE.*

		from   [dbo].[oe_emp] tblOEE with (SNAPSHOT)

		inner join [dbo].[oe_dep] tblOED with (SNAPSHOT)

			on tblOEE.[oe_emp_ssn] = tblOED.[oe_dep_emp_ssn]

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran



Explanation

  1. Add Table Hint (SNAPSHOT) to all In-Memory Table References
    • We added to oe_emp and oe_dep

 

Database Settings

Set Database Option “MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT” ON


set nocount on;
set XACT_ABORT on;

/*
	For our current database:
		Set Memory Optimized Elevate to Snapshot ON  
*/


ALTER DATABASE CURRENT 
	SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
go

begin tran

	begin try

		
		select top 10 tblOEE.*

		from   [dbo].[oe_emp] tblOEE --with (SNAPSHOT)

		inner join [dbo].[oe_dep] tblOED --with (SNAPSHOT)

			on tblOEE.[oe_emp_ssn] = tblOED.[oe_dep_emp_ssn]

		print 'Successful '

	end try

	begin catch

		print 'Exception'
		print '========='
		print 'ErrorNumber    :- ' + cast(ERROR_NUMBER() as varchar(600))
		print 'ErrorState     :- ' + cast(ERROR_STATE() as varchar(600))
		print 'ErrorProcedure :- ' + cast(isNull(ERROR_PROCEDURE(), -1) as varchar(600))
		print 'ErrorLine      :- ' + cast(isNull(ERROR_LINE(), -1) as varchar(600))
		print 'ErrorMessage   :- ' + cast(ERROR_MESSAGE() as varchar(600))

		
	end catch

rollback tran
go

/*
	Revert Database Setting
*/
ALTER DATABASE CURRENT 
	SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF  
go

Programmable Objects

Stored Procedure

In a follow-up post, we discuss how to effect the same covering when creating Stored Procedures.

 

Summary

In summary, to avoid the aforementioned errors, we can utilize Table Hints, specifically with (SNAPSHOT), on each query.

Or declaratively set the Database Option, MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, on each In-Memory Database.

 

References

  1. Database Features > In-Memory OLTP (In-Memory Optimization) > Memory-Optimized Tables
    Link
  2. Database Features > In-Memory OLTP (In-Memory Optimization) > Memory-Optimized Tables > Transactions with Memory-Optimized Tables
    Link

 

 

 

Transact SQL – Identify In-Memory Objects

Introduction

Let us identify In Memory Objects in our SQL Server Instance.

 

User Tables – sys.tables

Overview

In the sample code below, we identify In Memory Objects by checking the is_memory_optimized column in the sys.tables DMV.

Code



select
		 [table]
			= quotename(tblSS.[name])
				+ '.'
				+ quotename(tblST.[name])

from   sys.tables tblST

inner join sys.schemas tblSS

		on tblST.[schema_id] = tblSS.[schema_id]

where  tblST.is_memory_optimized = 1

order by
		[table] asc


Output

identifyinmemorytables-systables

User Tables & Indexes – Based on FileGroups

Overview

In the sample code below, we identify In Memory Objects by seeking out objects that are located on In-Memory Filegroups.

Code



select 

		  [object]
			= tblSS.[name]
				+ '.'
				+ tblSO.[name]

		, [objectType]
			= tblSO.[type_desc]


		, [indexName]
			= tblSI.[name]


		, [isPrimaryKey]
			= case
				when tblSI.[is_primary_key] = 1 then 'Yes'
				else 'No'
			  end

		, [indexType]
			= tblSI.[type_desc]
	
		, [fileGroup]
			= tblSFG.[name]

		, [fileGroupType]
			= tblSFG.[type_desc]

from   sys.objects tblSO

INNER JOIN sys.schemas tblSS

	ON tblSO.schema_id = tblSS.schema_id

inner join sys.indexes tblSI

	on tblSO.object_id = tblSI.object_id

inner join sys.partitions tblSP

	on  tblSI.object_id = tblSP.object_id
	and tblSI.index_id = tblSP.index_id

		
INNER JOIN sys.allocation_units tblSAU

	on tblSAU.container_id = tblSP.hobt_id
		 
INNER JOIN sys.filegroups tblSFG
 
	ON tblSFG.data_space_id = tblSAU.data_space_id 

where tblSFG.[type] = 'FX'



Output

identifyinmemoryobjects

 

 

User Tables

DML Stats

Overview

Review Statistics of DML Operations against In-Memory Tables.

Code


SELECT
	  [object]
		= quoteName(tblS.[name])
		   + '.'
		   + quoteName(tblO.[name])

        , tblXTPOS.*

FROM sys.dm_db_xtp_object_stats tblXTPOS

INNER JOIN sys.objects tblO

	ON tblXTPOS.object_id = tblO.object_id

INNER JOIN sys.schemas tblS

	ON tblO.schema_id = tblS.schema_id


 

Output

dmlstats

 

Index Stats

Overview

Review Statistics of DML Operations against In-Memory Tables.

Code





SELECT
        [object]
        = quoteName(tblS.[name])
           + '.'
           + quoteName(tblO.[name])
 
		, [index]
			= tblSI.[name]

		, [indexType]
			= tblSI.[type_desc]

		, [isPrimaryKey]
			= case
				when ( tblSI.[is_primary_key] = 1 ) then 'Y'
				else 'N'
			  end

        , [scansStarted]
			= tblXTPIS.scans_started	

		, [scansRetried]
			= tblXTPIS.scans_retries
			
		, [rowsReturned]
			= tblXTPIS.rows_returned

		, [rowsTouched]
			= tblXTPIS.rows_touched	

		/*

		--- Expiring -----
		, tblXTPIS.rows_expiring
		, tblXTPIS.rows_expired
		, tblXTPIS.rows_expired_removed	
		--- Expiring -----

		-- Phantom ----
		, tblXTPIS.phantom_scans_started	
		, tblXTPIS.phantom_scans_retries	
		, tblXTPIS.phantom_rows_touched	
		, tblXTPIS.phantom_expiring_rows_encountered	
		--, phantom_expired_rows_encountered	
		, tblXTPIS.phantom_expired_removed_rows_encountered	
		, tblXTPIS.phantom_expired_rows_removed
		-- Phantom ----

		*/

FROM sys.dm_db_xtp_index_stats tblXTPIS
 
INNER JOIN sys.objects tblO
 
    ON tblXTPIS.object_id = tblO.object_id

INNER JOIN sys.indexes tblSI
 
    ON  tblXTPIS.object_id = tblSI.object_id 
    AND tblXTPIS.index_id = tblSI.index_id 

INNER JOIN sys.schemas tblS
 
    ON tblO.schema_id = tblS.schema_id

order by
		  tblS.[name]
		, tblO.[name]
		, tblSI.[name]

Output

imtabledmlindexstats

Explanation:

  1. In-Memory tables does not support Clustered Indexes as all data is stored in memory
    • If Clustered,then memory will have to be continuously shuffled to ensure proper sequence
  2. It makes sense to review ScanStarted
    • Doing so is informative in terms of which indexes are actually being used
    • And, to gauge popularity
  3. The RowsReturned Column
    • Reviewing the RowsReturned column is also important to follow trend in terms of access method

Memory Usage Stats

Memory Usage Stats – Object

Overview

Review Memory allocated and in-use by In-Memory Tables.

Code


SELECT

	[object]
		= quotename(tblSS.name)
			+'.'
			+ quotename(tblSO.name)

    , [allocatedMB]
		= SUM(tblXTPMC.[allocated_bytes]) / (1024* 1024)

	, [usedMB]
		= SUM(tblXTPMC.[used_bytes]) / (1024 * 1024)

FROM  sys.objects tblSO

INNER JOIN sys.schemas tblSS
 
	ON tblSO.schema_id = tblSS.schema_id 

INNER JOIN sys.indexes tblSI
 
	ON tblSO.object_id = tblSI.object_id 

INNER JOIN sys.dm_db_xtp_memory_consumers tblXTPMC 

	ON  tblSI.object_id = tblXTPMC.object_id
	AND tblSI.index_id  = tblXTPMC.index_id

GROUP BY 
		  quotename(tblSS.name)
		, tblSO.schema_id
		, tblSO.object_id
		, tblSO.[name]
		
ORDER BY 
		  quotename(tblSS.name)
		, tblSO.[name]
;


Output

memoryallocatedandinusedbyobject

Memory Usage Stats – Index

Overview

Review Memory allocated and in-use by In-Memory Table Indexes.

Code


SELECT

	[object]
		= quotename(tblSS.name)
			+'.'
			+ quotename(tblSO.name)

	, [index]
		= tblSI.name

	, [indexType]
		= tblSI.[type_desc]

    , [isPrimaryKey]
        = case
            when tblSI.[is_primary_key] = 1 then 'Yes'
            else 'No'
            end

    , [allocatedMB]
		= SUM(tblXTPMC.[allocated_bytes]) / (1024* 1024)

	, [usedMB]
		= SUM(tblXTPMC.[used_bytes]) / (1024 * 1024)

FROM  sys.objects tblSO

INNER JOIN sys.schemas tblSS
 
	ON tblSO.schema_id = tblSS.schema_id 

INNER JOIN sys.indexes tblSI
 
	ON tblSO.object_id = tblSI.object_id 

INNER JOIN sys.dm_db_xtp_memory_consumers tblXTPMC 

	ON  tblSI.object_id = tblXTPMC.object_id
	AND tblSI.index_id  = tblXTPMC.index_id

GROUP BY 
		  quotename(tblSS.name)
		, tblSO.schema_id
		, tblSO.object_id
		, tblSO.[name]
		, tblSI.name
		, tblSI.[type_desc]
		, tblSI.[is_primary_key]
		
ORDER BY 
		  quotename(tblSS.name)
		, tblSO.[name]
		, tblSI.name


Output

memoryallocatedandinusedbyobjectandindex

Programmable Objects – sys.modules

Overview

In the sample code below, we identify In Memory Objects by checking the is_memory_optimized column in the sys.tables DMV.

Code



/*

	sys.sql_modules

		execute_as_principal_id

			Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. 
			This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.

*/

select 
		  [object]
			= quoteName(tblSS.[name])
				+ '.'
				+ quoteName(object_name(tblSSM.[object_id]))

		, [type]
			= tblSO.type_desc

		, [createDate]
			= tblSO.[create_date]

		, [isSchemaBound]
			= case
					when tblSSM.is_schema_bound = 1 then 'Y'
					else 'N'
			  end	

		, [principal]
			= case
			 
					when ( tblSSM.execute_as_principal_id is null ) 
						then tblSS.[name]
			  
					when  ( tblSSM.execute_as_principal_id = -2) 
						then '--Shared--'

					else user_name(tblSSM.execute_as_principal_id)

						  
			  end			 
		      
		, [definition]
			= tblSSM.[definition]

from  sys.objects tblSO

inner join sys.schemas tblSS

		on tblSO.[schema_id] = tblSS.[schema_id]

inner join sys.sql_modules tblSSM

		on tblSO.[object_id] = tblSSM.[object_id]

where tblSSM.[uses_native_compilation] = 1

Output

identifynativecompiledobjects

Loaded Modules – sys.dm_os_loaded_modules

Overview

In the sample code below, we identify In Memory Objects by reviewing loaded OS Modules.

The relevant DMVs are sys.dm_os_loaded_modules and sys.dm_os_virtual_address_dump.

 

Code



; with cteOSLM
(
	  [modulename]
	, [description]
    , [base_address]
	, [filenameFull]
	, [filename]
	, [filenameMassaged]
)
as
(

	SELECT
			  [name]

			, [description]

			, [base_address]

			, [filenameFull] = tblOSLM.[name]

			, [filename]
				= reverse(left(reverse(tblOSLM.[name]),
                    charindex('\',reverse(tblOSLM.[name]), 1) - 1))

			, [filenameMassaged]
				= replace(
							replace(
									 reverse(left(reverse(tblOSLM.[name]),
										charindex('\',reverse(tblOSLM.[name]), 1) - 1))
										, '_'
									, '.'
									)
							, '.dll'
							, ''
						)				

	
	FROM   sys.dm_os_loaded_modules tblOSLM 

	WHERE  tblOSLM.[description] = 'XTP Native DLL'

	
)

, cteOSLMObject
(
	  [modulename]
	, [description]
    , [base_address]
	, [filenameFull]
	, [filename]
	, [filenameMassaged]

	, [objectID] 
	, [databaseID] 
	, [objectType]

)
as
(

	SELECT 

		  	  [modulename]
			, [description]
			, [base_address]
			, [filenameFull]
			, [filename]
			, [filenameMassaged]


			, [objectID] 
				= PARSENAME([filenameMassaged], 1)

			, [databaseID] 
				= PARSENAME([filenameMassaged], 2)
			
			, [objectType]
				= case PARSENAME([filenameMassaged], 3)
						when 't' then 'Table'
						when 'p' then 'Procedure'
						when 'f' then 'Function'
						else PARSENAME([filenameMassaged], 3)
				  end 

	from   cteOSLM

)
, cteVirtualAddress
(
	  [region_allocation_base_address]
	, [regionSizeInBytes]
)
as
(
	select 
			  tblOSVAD.[region_allocation_base_address]
			, [regionSizeInBytes]
				= sum(tblOSVAD.region_size_in_bytes)
	
	from   sys.dm_os_virtual_address_dump tblOSVAD
	
	group by
	 
			tblOSVAD.[region_allocation_base_address]

)	 
SELECT 

		  tblOSLM.[description]

		, tblOSLM.[modulename]

		, tblOSLM.[filename]


		, [database]
			= case
				when tblOSLM.[databaseID] = 32767 then 'Resource DB'
				else db_name(tblOSLM.[databaseID])
			   end

		, [objectName]
			= quoteName
				(
				    object_schema_name
					(
					  tblOSLM.objectID
					, tblOSLM.databaseID
					)
				)
				+ '.'
				+ quoteName
				(
					object_name
					(
					   tblOSLM.objectID
					 , tblOSLM.databaseID
					)
				)

		, tblOSLM.[objectType]

		, [sizeInKB]
			= (tblOSVAD.[regionSizeInBytes])
				/ ( 1024 )

FROM   cteOSLMObject tblOSLM 

INNER JOIN cteVirtualAddress tblOSVAD

		on tblOSLM.[base_address] = tblOSVAD.[region_allocation_base_address]

order by

			  [database]
			, [objectName]


Output

osloadedmodules

 

Explanation

  1. We can see that we can the SQL Server Engine generates Database Object specific modules (dlls)
  2. The modules are aptly named once one know where to look
  3. And, they are very small in size

 

Summary

In summary, to identify In Memory Objects we have a couple of tracks we can take.

Those pathways includes:

  1. Checking Table – sys.tables
    • Column :- is_memory_optimized
  2. Checking objects that are sitting on Memory Optimized Filegroup by relying on the sys.filegroups.

We reviewed user usage by digging into the sys.dm_db_xtp_object_stats & sys.dm_db_xtp_index_stats.

We also touched on memory consumed by In Memory Objects and Indexes by exploring the sys.dm_db_xtp_memory_consumers view.

To identify natively compiled objects we narrow in on the uses_native_compilation column of the sys.sql_modules view

To dig a bit deeper and identify OS Modules that are produced once In-Memory tables are compiled, we look into sys.dm_os_loaded_modules and to size up them up we check the region_size_in_bytes column in the sys.dm_os_virtual_address_dump table.

 

Source Code Repository

GitHub

Blogs are not where to keep code, and so for easier and more polish consumption shipped out to GitHub.

Here is the Repository.

 

Dedicated

Again, the good thing about blogging is that one publicly acknowledges the source of Information.

Here is mine:

  1. SqlHints.com, Basavaraj Biradar
    • Working with In-Memory OLTP (a.k.a. Hekaton) enabled Databases, Memory Optimized Tables and Natively Compiled Stored Procedures and it’s Internals with extensive list of Examples – Sql Server 2014
      Link
  2.  Help: SQL Server, Balmukund
    • A-Z of In-Memory OLTP : Behind the scenes
      Link

 

References

Microsoft

    1. Dynamic Management Views and Objects
      • Transact-SQL Reference (Database Engine) > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL)
        Memory-Optimized Table Dynamic Management Views (Transact-SQL)
        Link

        • sys.dm_db_xtp_memory_consumers (Transact-SQL)
          Link
        • sys.dm_db_xtp_index_stats ( Transact SQL )
          Link
        • sys.dm_db_xtp_object_stats ( Transact-SQL )
          Link
        • sys.memory_optimized_tables_internal_attributes ( Transact-SQL )
          Link
    2. Developer Network
      • Database Features > In-Memory OLTP (In-Memory Optimization)  > Memory-Optimized Tables
        Native Compilation of Tables and Stored Procedures
        Link

Technical: Microsoft – SQL Server – v2014 (In Memory Table) – Benchmark Simple Queries

 

 

Technical: Microsoft – SQL Server – v2014 (In Memory Table) – Benchmark Simple Queries

 

Background

Comparing Simple SQL Server queries against traditional and in-memory tables.

 

Generate Data to query for

We will like our test queries to request data that is representative of our existing data. And, as such we will get a sampler of existing data.

This is easy to do once one discovers MS SQL Server tablesample keyword.

 

bcp "select distinct top 100000 quotename(firstname, '\"') as firstname, quotename(lastname, '\"') as lastname, quotename(emailaddress, '\"') as emailAddress, quotename(convert(varchar(10), dateofBirth, 101) , '\"')  as dateofBirth from   [dbo].[personTraditionalClustered] tablesample (100000 rows)  with (nolock)" queryout e:\tmp\sampleData.txt -c  -S (local) -d DBLabInMemory -T -t ","


 

Please note the following:

  • Quest Benchmark factory likes for strings to be quoted and as such we will use quotename to specify that our chosen strings and date columns be surrounded in double quotes
  • We are using (tablesample 100000 rows) to randomly get one hundred thousand rows

 

If your strings are not quoted, you might get an error that resembles the following.

IfFileContentsAreNotQuoted

 

Not quite descriptive nor informational, but an error is an error.

 

Sample Data



"Ezekiel","Brennan","Wallace@risus.edu","01/10/1994"
"Henry","Crane","Craig@ut.org","04/23/1963"
"Ulla","Workman","Chaney@vestibulum.org","10/10/1955"
"Ralph","Gamble","Avye@erat.edu","06/03/1929"
"Diana","Vang","Ori@mattis.gov","07/31/1921"
"Kasper","Sanford","Ori@morbi.edu","06/05/1975"
"Nicholas","Daniel","Joshua@posuere.net","01/19/1903"
"Erich","Howe","Kirestin@placerat.com","07/24/1969"
"Piper","Hill","Austin@id.gov","12/30/2012"
"Baxter","Key","Channing@placerat.org","01/29/1900"


 

Prepare Microsoft SQL Server

There are a couple of areas that we should cover prior to load testing.

 

Microsoft SQL Server Stored Procedures

Here are the Stored Procedures that we will use to retrieve data.

Stored Procedure – Traditional


if object_id('dbo.usp_Traditional_getPerson_FilteredOnName') is not null
begin
	drop procedure dbo.usp_Traditional_getPerson_FilteredOnName
end
go

create procedure dbo.usp_Traditional_getPerson_FilteredOnName
(
	@firstname varchar(40)
)
as

	select
			   [firstname]
			  ,[lastname]
			  ,[emailAddress]
			  ,[phoneNumber]
			  ,[addressStreet]
			  ,[addressCity]
			  ,[addressState]
			  ,[addressPostalCode]
			  ,[dateofBirth]
			  ,[uniqueID]
		from   [dbo].[personTraditionalClustered] tblPerson
		where  tblPerson.firstname = @firstname


end

go 

Stored Procedure – dbo.usp_Traditional_getPerson_FilteredOnUniqueID


if object_id('dbo.usp_Traditional_getPerson_FilteredOnUniqueID') is not null
begin
 drop procedure dbo.usp_Traditional_getPerson_FilteredOnUniqueID
end
go

create procedure dbo.usp_Traditional_getPerson_FilteredOnUniqueID
(
     @uniqueID uniqueIdentifier
)
as
 select 
    
       [firstname]
      ,[lastname]
      ,[emailAddress]
      ,[phoneNumber]
      ,[addressStreet]
      ,[addressCity]
      ,[addressState]
      ,[addressPostalCode]
      ,[dateofBirth]
      ,[uniqueID]

 from [dbo].[personTraditionalClustered] tblPerson

 where tblPerson.UniqueID = @uniqueID


go


Stored Procedure – Memory Optimized



if object_id('dbo.usp_InMemory_getPerson_FilteredOnName') is not null
begin
	drop procedure dbo.usp_InMemory_getPerson_FilteredOnName
end
go

create procedure dbo.usp_InMemory_getPerson_FilteredOnName
(
	@firstname varchar(40)
)
with native_compilation, schemabinding, execute as owner
as

	begin atomic
	with (transaction isolation level=snapshot, language=N'us_english')


	select
			   [firstname]
			  ,[lastname]
			  ,[emailAddress]
			  ,[phoneNumber]
			  ,[addressStreet]
			  ,[addressCity]
			  ,[addressState]
			  ,[addressPostalCode]
			  ,[dateofBirth]
			  ,[uniqueID]
		from   [dbo].[personInMemory] tblPerson
		where  tblPerson.firstname = @firstname


end

go




 

Stored Procedure – dbo.usp_InMemory_getPerson_FilteredOnUniqueID


if object_id('dbo.usp_InMemory_getPerson_FilteredOnUniqueID') is not null
begin
     drop procedure dbo.usp_InMemory_getPerson_FilteredOnUniqueID
end
go

create procedure dbo.usp_InMemory_getPerson_FilteredOnUniqueID
(
     @uniqueID uniqueIdentifier
)
with native_compilation, schemabinding, execute as owner
as

 begin atomic
 with (transaction isolation level=snapshot, language=N'us_english')


      select 
          [firstname]
         ,[lastname]
         ,[emailAddress]
         ,[phoneNumber]
         ,[addressStreet]
         ,[addressCity]
         ,[addressState]
         ,[addressPostalCode]
         ,[dateofBirth]
         ,[uniqueID]
     from [dbo].[personInMemory] tblPerson
     where tblPerson.UniqueID = @uniqueID

end

go




Quest Benchmark Factory

Scenarios

In Quest Benchmark, we reference the generated sample data file by using BFFFileArray. Please keep in mind that it allows us to reference each column individually by specifying its position in the file.

 

Scenario SQL
SQLStmt_FirstName_Traditional select *
from [dbo].[personTraditionalClustered] tblPerson
where tblPerson.firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
 SQLStmt_FirstName_InMemory select *
from [dbo].[personInMemory] tblPerson
where tblPerson.firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
SQLStmt_UniqueID_Traditional select *
from [dbo].[personTraditionalClustered] tblPerson
where tblPerson.uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
 SQLStmt_UniqueID_InMemory select *
from [dbo].[personInMemory] tblPerson
where tblPerson.uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
SQLSP_FirstName_Traditional  exec dbo.usp_Traditional_getPerson_FilteredOnName
@firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
 SQLSP_FirstName_InMemory  exec dbo.usp_InMemory_getPerson_FilteredOnName
@firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
SQLSP_UniqueID_Traditional exec dbo.usp_Traditional_getPerson_FilteredOnUniqueID
@uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
 SQLSP_UniqueID_Traditional exec dbo.usp_InMemory_getPerson_FilteredOnUniqueID
@uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’

 

 

Latencies

Here is how we configured latency for each of our Transactions:

LoadSetup-Latency

Explanation:

  • We are setup for zero delay (No Delay).  In a whole lot of cases, one adds Think Time and the like, but since we know we are employing the Free Version of Quest Benchmark and we can only go up to 20 users, and not 100 users as the actual product allows, we wanted to stress the system a bit

 

 

 

SQL Server – Benchmark – Profiler

In certain cases, it is important to review the client Application interaction with SQL Server and glean how the queries are being submitted.

SQL Statements

SqlServerProfiler-SelectStatement

 

Stored Procedure Statements

SQLServerProfiler-StoredProcedureExecutionThroughExecStatement

 

SQL Server – Benchmark – Results

Here is the results of running the benchmark.

Summary

QuestBenchMark-ResultsSummary

Graph

Response Time vs Userload

 

QuestBenchmark-SQLServerCompare

Tabular

 

ID Scenario Response Time in millisecond for 20 Userload (with 1 being best)
1 SQLStmt_FirstName_Traditional 6  ( 31 milliseconds )
2  SQLStmt_FirstName_InMemory 5  ( 27 milliseconds )
3 SQLStmt_UniqueID_Traditional 2  ( 3 milliseconds )
4  SQLStmt_UniqueID_InMemory 4  ( 6 milliseconds )
5 SQLSP_FirstName_Traditional 6  ( 31 milliseconds )
6  SQLSP_FirstName_InMemory 8  ( 38 milliseconds )
7 SQLSP_UniqueID_Traditional 3 ( 4 seconds)
8  SQLSP_UniqueID_InMemory 1 ( 0 seconds )

 

 

Explanation:

  • The queries that filters on UniqueID, a uniqueidentifier a column, are the fastest
  • In the traditional table we are able to create clustered indexes and we created one on the UniqueIDs column.
  • The query on the in-memory table against the unclustered hash index did better than the one against the traditional table with a clustered indexed
  • Due to our tool’s usage of prepared statements, we did not pay a price on SQL Statements

 

SQL Server – Query Plan

Fetch on Name

Query

Here is what our query on name looks like:

declare @firstname varchar(40)

set @firstname = 'David'

exec dbo.usp_Traditional_getPerson_FilteredOnName
	   @firstname = @firstname

exec dbo.usp_InMemory_getPerson_FilteredOnName
	   @firstname = @firstname

 

Estimated Execution Plan

Traditional Table:

FecthOnNameAgainstTraditional

 

In-Memory Table:

FetchOnNameAgainstInMemoryTable

 

Explanation:

  • The query plan for in-memory is a bit simpler
  • For In-Memory, it consists of a single index seek
  • For Traditional, it does an index-seek and a clustered-index lookup to get the additional columns we are querying for, and a nested loops inner join to combine the results of the two
  • Interestingly, the traditional query is also suggesting that we create a covering index to avoid the two index retrievals

Statistics I/O

Table 'personTraditionalClustered'. Scan count 1, logical reads 3554, 
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical 
reads 0, lob read-ahead reads 0.

Explanation

  • The traditional table registers I/O metrics, but not so for in-memory tables

 

 

Fetch on UniqueID

Query

Here is what our query on name looks like:

declare @uniqueID uniqueIdentifier

set @uniqueID = newid()

exec dbo.usp_Traditional_getPerson_FilteredOnUniqueID
	   @uniqueID = @uniqueID

exec dbo.usp_InMemory_getPerson_FilteredOnUniqueID
	   @uniqueID = @uniqueID

 

Estimated Execution Plan

Traditional Table:

FecthOnUniqueIDAgainstTraditional

 

In-Memory Table:

FecthOnUniqueIDAgainstInMemory

 

Explanation:

  • The query plans are very similar
  • Fo traditional table, we have a clustered index seek
  • For In-Memory table, we have an index seek
  • Not so sure that SQL Server is properly costing In-Memory queries

 

Statistics I/O

Table 'personTraditionalClustered'. Scan count 1, logical reads 3, 
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical 
reads 0, lob read-ahead reads 0.

Explanation

  • The traditional table registers a very small I/O metric, but not so for in-memory tables

 

 

SQL Server – Wait Stats

In-Memory

Activity Monitor

For in_memory selects, here are our wait stats

ResourceWaits

 

Explanation:

  1. Table access is performed via SQLCLR
  2. Return of selected data is noted as Network I/O

 

Dynamic Management View

Using the stolen query https://danieladeniji.wordpress.com/2013/06/19/technical-microsoft-sql-server-wait-stats-cx-packet/, here is raw wait type data:

waitStatsDMV

Explanation:

  1. Again, CLR_AUTO_EVENT

 

Summary

Queries targeting UniqueIdentifier datatype column seem to run consistently better than those that are targeting the varchar datatype.

For in-memory tables because we can only create indexes on BIN2 columns.  As Binary means binary and does not support case-insensitive searches, for actual business use-cases, workarounds will have to be understood and diligently employed.

In a follow-up posting will like to compare In-Memory searches where an index is available against ones where indexes are not created.

I think our Tool choice is not fully supporting Stored Procedure calls and it is treating them as Prepared calls.

 

References

References – Microsoft – SQL Server – Memory Optimized DB

References – Quest Benchmark Factory

References – Microsoft – SQL Server – Transact SQL – Table Sample