How to find index usage from cached plan in SQL Server

Today, I would like to share a script to identify the usage of index from cached plan in SQL Server. This would help us to identify the places (procs/functions etc) where the index is being used. One interesting use-case is to identify the index usage that has been introduced recently during the performance optimization.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE
(
	DatabaseName,SchemaName,TableName,IndexName,
	QueryText,QueryPlan,CacheObjectType,ObjectType
)
AS
(
SELECT	
        RelOp1.op.value(N'@Database', N'varchar(128)') AS DatabaseName,
	RelOp1.op.value(N'@Schema', N'varchar(128)') AS SchemaName,
	RelOp1.op.value(N'@Table', N'varchar(128)') AS TableName,
	RelOp1.op.value(N'@Index', N'varchar(128)') AS IndexName,
	cp.TEXT AS QueryText,cp.query_plan AS QueryPlan,
	cp.cacheobjtype AS CacheObjectType,cp.objtype AS ObjectType
FROM (SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' ) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') RelOp1 (op)
)
SELECT	QueryPlan,QueryText,CacheObjectType,	ObjectType,
		DatabaseName,	SchemaName,TableName,	IndexName
FROM CachedPlansCTE
WHERE CacheObjectType = N'Compiled Plan'
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search for a particular index*/
and (IndexName like '%Indexname%')
/* if you need to search ONLY for SELECT queries*/
and (QueryText not like '%insert%')
and (QueryText not like '%update%')
*********************************************************
*/
OPTION (MAXDOP 1)

See Also:
How to identify Missing Index from Cached Plan in SQL Server
How do you find cached plan for a procedure in SQL Server

Advertisements

In-Memory OLTP: Compatibility Check for Datatypes in SQL Server

Hope you enjoyed my earlier posts on
In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014
In-Memory OLTP: Compatibility Check for SQL Server and Database

Here, as continuation of compatibility check, we are moving further one more step on Datatype compatibility.

The below script is primarily focusing on major limitations like unsupported datatypes,computed columns,sparse columns, unavailability of primary keys and presence of clustered index. The script has been developed for SQL Server 2014 version(if there are any of the above limitations removed in later versions, you may need to change the script accordingly.).

Note: Please change the result view as “Results To Text(Ctrl + T)” for better readability of the result.


PRINT '*******************************************************'
PRINT 'DATATYPE COMPATIBILITY CHECK'
PRINT '*******************************************************'

PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED DATATYPES. '
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE 
	(TYPE_NAME(USER_TYPE_ID) IN 
            ('IMAGE','TEXT','NTEXT','DATETIMEOFFSET','XML','GEOMETRY',
             'DATETIMEOFFSET','GEOGRAPHY','TIMESTAMP','SQL_VARIANT'))
	OR
	(TYPE_NAME(user_type_id) IN 
            ('VARCHAR','CHAR','NVARCHAR','NCHAR','VARBINARY') AND MAX_LENGTH = -1)
	OR
	/*FILESTREAM can only be enabled with VARBINARY(MAX), however, the defined form of datatype is
		not supported in In-Memory Tables, provided the below for FILESTREAM/FILETABLE for completeness,*/
	(TYPE_NAME(USER_TYPE_ID) IN ('VARBINARY') AND MAX_LENGTH = -1 AND (IS_FILESTREAM =1 OR IS_FILETABLE = 1))
	)

--COMPUTED COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED COMPUTED COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE IS_COMPUTED =1)

--SPARSE COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE SPARSE COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE is_sparse =1) 

-- ABSENCE OF PRIMARY KEY
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY DO NOT HAVE PRIMARY KEYS'
SELECT NAME FROM SYS.TABLES WHERE NAME NOT IN 
	(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE ='PRIMARY KEY')

--PRESENCE OF CLUSTERED INDEX
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE CLUSTERED INDEX'
SELECT B.NAME FROM SYS.INDEXES A
INNER JOIN SYS.TABLES B ON A.OBJECT_ID =B.OBJECT_ID WHERE A.TYPE_DESC = 'CLUSTERED'

In-Memory OLTP: Compatibility Check for SQL Server and Database

In continuation with my last post, I would like to further move on to the topic by starting with the limitations of In-Memory OLTP feature.

“Entire Data in Memory” looks such a wonderful idea to improve the performance by reducing the locking/latching etc, however, Is it true to adopt for all systems? NO. There are lots of restrictions (at least in SQL Server 2014 and hoping many would be taking off in future versions) with In-Memory OLTP feature.
You can refer those limitation in the below MSDN

Transact-SQL Constructs Not Supported by In-Memory OLTP

Here, I would like to share a mechanism to verify the compatibility level of existing environment to migrate to In-Memory Tables. By now(once you read the above link), as you all know the limitations are more, I would like to split the script in multiple snippets for simplicity.

Please refer the below script to verify the compatibility level of your environment at Server and Data base level.


SET NOCOUNT ON

PRINT '************************************************'
PRINT 'DATABASE SERVER COMPATIBILITY CHECK'
PRINT '************************************************'
IF NOT EXISTS(SELECT 1 FROM (SELECT SUBSTRING(@@VERSION,22,4)) A (VERSIONNUMBER) WHERE VERSIONNUMBER >=2014)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN SQL SERVER 2014 OR LATER VERSIONS.'

IF EXISTS(SELECT 1 FROM (SELECT @@VERSION) A (BITVERSION) WHERE CHARINDEX('(64-BIT)',BITVERSION)=0)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN 64 BIT VERSIONS.'

IF EXISTS(SELECT * FROM MASTER.SYS.SERVER_TRIGGERS)
	PRINT HOST_NAME() + ' IS ENABLED WITH SERVER LEVEL TRIGGER AND IN-MEMORY OLTP WILL NOT SUPPORT SERVER LEVEL TRIGGERS.'


PRINT '************************************************'
PRINT 'DATABASE COMPATIBILITY CHECK'
PRINT '************************************************'
	
IF (DB_NAME() NOT IN (SELECT DB_NAME(DATABASE_ID) FROM SYS.MASTER_FILES WHERE TYPE_DESC = 'FILESTREAM'))
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT WITH MEMORY_OPTIMIZED_DATA FILEGROUP. THE CURRENT DATABASE WILL NOT SUPPORT.'
	
IF (DB_NAME() IN (SELECT NAME FROM SYS.DATABASES WHERE IS_MERGE_PUBLISHED & IS_SUBSCRIBED & IS_DISTRIBUTOR & IS_PUBLISHED = 1))
	PRINT DB_NAME() + 'HAS BEEN REPLICATED AND IN-MEMORY OLTP WILL NOT SUPPORT REPLICATED DATABASE'

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE DB_ID () = DATABASE_ID AND IS_AUTO_CLOSE_ON = 1)
	PRINT DB_NAME() + 'HAS BEEN CONFIGURED WITH AUTO CLOSE OPTION AND IN-MEMORY OLTP WILL NOT SUPPORT AUTO_CLOSE FEATURE'

We will cover more on other aspects like datatypes/constraints etc in future posts, until then, happy learning….