Feb 8, 2015

Get all the indexes name used by a query or Stored procedure in sql server

This script helps you to know that a query or stored procedure is utilizing the specific indexes created by you.  What indexes are no longer used etc?


Script to get list of all the indexes used by a given query SQL Server:

--Replace with your secript. If there is any single quote in the your sql script replace it with two single quotes.
DECLARE @vcQueryText AS VARCHAR(MAX) = 'SELECT * FROM Student'

EXECUTE (@vcQueryText)

;WITH xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS PD)
SELECT TT.C.value('@Database', 'SYSNAME') AS DatabaseName
      ,TT.C.value('@Schema', 'SYSNAME') AS SchemaName
      ,TT.C.value('@Table', 'SYSNAME') AS TableName
      ,TT.C.value('@Index', 'SYSNAME') AS IndexName
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) P
CROSS APPLY P.query_plan.nodes('//PD:Object') TT(C)
WHERE TEXT = @vcQueryText
      AND TT.C.value('@Index', 'SYSNAME') IS NOT NULL

Script to get list of all the indexes used by a given stored procedure in sql server:

DECLARE @vcProcedureName AS SYSNAME = 'YourProcedureName';

WITH xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS PD)
SELECT TT.C.value('@Database', 'SYSNAME') AS DatabaseName
       ,TT.C.value('@Schema', 'SYSNAME') AS SchemaName
       ,TT.C.value('@Table', 'SYSNAME') AS TableName
       ,TT.C.value('@Index', 'SYSNAME') AS IndexName
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle) P
CROSS APPLY P.query_plan.nodes('//PD:Object') TT(C)
WHERE objectid = OBJECT_ID(@vcProcedureName)
       AND TT.C.value('@Index', 'SYSNAME') IS NOT NULL


Note:  To get the output first execute your stored procedure.

No comments:

Post a Comment