Importent Procedure for Performance Tuning
SELECT OBJECT_NAME([PS].[object_id], [PS].[database_id]) AS [ProcedureName],
[PS].[execution_count] AS [ProcedureExecutes],
[QS].[plan_generation_num] AS [VersionOfPlan],
[QS].[execution_count] AS [ExecutionofCurrentPlan],
SUBSTRING([ST].[Text],([QS].[statement_start_offset]/2)+1,
((CASE [statement_start_offset] WHEN -1 THEN DATALENGTH([ST].[text])
ELSE [QS].[statement_end_offset] END
- [QS].[statement_start_offset])/2)+1) AS [StatementText],
[QS].[statement_start_offset] AS [Offset],
[QS].[statement_end_offset] AS [Offset_end],
[QP].[query_plan] AS [Query Plan XML],
[QS].[query_plan_hash] AS [Query Plan Fingerprint]
FROM [sys].[dm_exec_procedure_stats] AS [PS]
JOIN [sys].[dm_exec_query_stats] AS [QS]
ON [PS].[Plan_handle] = [QS].[Plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan] ([QS].[Plan_handle]) AS [QP]
CROSS APPLY [sys].[dm_exec_sql_text] ([QS].[sql_handle]) AS [ST]
WHERE [PS].[database_id] = DB_ID()
--AND OBJECT_NAME([PS].[object_id], [PS].[database_id])
--NOT IN (N'ProcedurePlans',N'RecompileEvents')
AND OBJECT_NAME([PS].[object_id], [PS].[database_id]) = 'USP_SearchAgreements_UPDATED_v4'
ORDER BY [ProcedureName],[QS].[statement_start_offset]
SELECT OBJECT_NAME([PS].[object_id], [PS].[database_id]) AS [ProcedureName],
ReplyDelete[PS].[execution_count] AS [ProcedureExecutes],
[QS].[plan_generation_num] AS [VersionOfPlan],
[QS].[execution_count] AS [ExecutionofCurrentPlan],
SUBSTRING([ST].[Text],([QS].[statement_start_offset]/2)+1,
((CASE [statement_start_offset] WHEN -1 THEN DATALENGTH([ST].[text])
ELSE [QS].[statement_end_offset] END
- [QS].[statement_start_offset])/2)+1) AS [StatementText],
[QS].[statement_start_offset] AS [Offset],
[QS].[statement_end_offset] AS [Offset_end],
[QP].[query_plan] AS [Query Plan XML],
[QS].[query_plan_hash] AS [Query Plan Fingerprint]
FROM [sys].[dm_exec_procedure_stats] AS [PS]
JOIN [sys].[dm_exec_query_stats] AS [QS]
ON [PS].[Plan_handle] = [QS].[Plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan] ([QS].[Plan_handle]) AS [QP]
CROSS APPLY [sys].[dm_exec_sql_text] ([QS].[sql_handle]) AS [ST]
WHERE [PS].[database_id] = DB_ID()
--AND OBJECT_NAME([PS].[object_id], [PS].[database_id])
--NOT IN (N'ProcedurePlans',N'RecompileEvents')
AND OBJECT_NAME([PS].[object_id], [PS].[database_id]) = 'USP_SearchAgreements_UPDATED_v4'
ORDER BY [ProcedureName],[QS].[statement_start_offset]