Wednesday, 7 June 2017

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]




WHAT OTHERS ARE READING/RELATED TO THIS:

1 comment:

  1. 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]

    ReplyDelete