使用 sys.dm_exec_procedure_stats 识别未使用的过程 [英] Identifying unused procedures using sys.dm_exec_procedure_stats

问题描述

我有一个表，一年多来，它每小时更新一次来自 sys.dm_exec_procedure_stats 的数据.

I have a table that has been updated hourly with data from sys.dm_exec_procedure_stats for over a year now.

如果我没有看到此表中列出的程序，我是否可以 99% 以上确定它不再使用?我的意思是，我知道可能会有一些疯狂的边缘情况，有人设计了一个进程来运行一个 proc，然后立即将它从缓存中删除，这样我的进程就不会记录它的使用情况.我显然愿意忽略那些愚蠢的边缘情况.

If I don't see a procedure listed in this table, can I be more than 99% sure it is no longer used? I mean, I understand there may be some crazy edge case scenario where someone designed a process to run a proc and then immediately remove it from the cache so its usage never gets recorded by my process. I'm obviously willing to ignore those kinds of silly edge case scenarios.

推荐答案

sys.dm_exec_procedure_stats DMV 反映了过程缓存的当前状态.当相应的缓存条目被删除时，SQL Server 会从此 DMV 中删除条目，因此您可能会通过获取此 DMV 的定期快照而错过存储过程的执行.此外，不会捕获带有 RECOMPILE 的存储过程.

The sys.dm_exec_procedure_stats DMV reflects current state of the procedure cache. SQL Server removes entries from this DMV when the corresponding cache entry is removed so you might miss stored procedure executions by taking periodic snapshots of this DMV. Also, stored procs with RECOMPILE would not be captured.

识别所有存储过程执行的更可靠方法是使用写入文件目标的服务器端跟踪.然后可以汇总跟踪数据并将其保存到表格中.

A more reliable method to identify all stored proc executions is with a server-side trace that writes to a file target. The trace data can then be summarized and saved to a table.

以下是用于 module_end 事件、支持 SQL 对象和 PowerShell 脚本的 XE 跟踪的示例 DDL.PowerShell 脚本从跟踪文件中汇总存储的 proc 执行，并将汇总数据保存到永久表中以供分析.可以定期安排 PS 脚本来处理滚动跟踪文件.

Below is example DDL for an XE trace of module_end events, supporting SQL objects, and a PowerShell script. The PowerShell script summarizes stored proc executions from the trace file(s) and saves summary data to a permanent table for analysis. The PS script can be scheduled periodically to process rolled over trace files.

USE YourDatabase;

CREATE EVENT SESSION [StoredProcedureExecutions] ON SERVER
ADD EVENT sqlserver.module_end(
    WHERE ([package0].[not_equal_uint64]([source_database_id],(32767)) AND [sqlserver].[equal_i_sql_ansi_string]([object_type],'P')))
ADD TARGET package0.event_file(SET filename=N'D:\SqlTraceFiles\StoredProcedureExecutions',max_file_size=(100),max_rollover_files=(5))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

ALTER EVENT SESSION [StoredProcedureExecutions] ON SERVER 
     STATE=START;

CREATE TABLE dbo.ModuleEndSummaryStaging(
      source_database_id smallint NOT NULL
    , object_id int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NOT NULL
    , min_timestamp datetimeoffset NOT NULL
    , max_timestamp datetimeoffset NOT NULL
    CONSTRAINT PK_ModuleEndSummaryStaging PRIMARY KEY(
          source_database_id
        , object_id
        , object_name
    )
);
GO

CREATE TABLE dbo.StoredProcedureExecutionHistory(
      DatabaseName sysname NOT NULL
    , SchemaName sysname NOT NULL
    , ObjectName sysname NOT NULL
    , source_database_id smallint NOT NULL
    , object_id int NOT NULL
    , object_name sysname NOT NULL
    , ExecutionCount bigint
    , FirstExecutionTimestamp datetimeoffset NOT NULL
    , LastExecutionTimestamp datetimeoffset NOT NULL
    , CONSTRAINT PK_StoredProcedureExecutionHistory PRIMARY KEY (
          source_database_id
        , object_id
        , object_name
        , DatabaseName
        , SchemaName
        , ObjectName)
);
GO

CREATE OR ALTER PROCEDURE dbo.MergeStoredProcedureExecutionHistory
AS
SET NOCOUNT ON;
MERGE dbo.StoredProcedureExecutionHistory AS target
USING  (
    SELECT 
          source_database_id
        , object_id
        , object_name
        , execution_count
        , min_timestamp
        , max_timestamp
        , COALESCE(DB_NAME(source_database_id), N'') AS DatabaseName
        , COALESCE(OBJECT_SCHEMA_NAME(object_id, source_database_id), N'') AS SchemaName
        , COALESCE(OBJECT_NAME(object_id, source_database_id), N'') AS ObjectName
    FROM dbo.ModuleEndSummaryStaging
    ) AS source ON
        source.source_database_id = target.source_database_id
        AND source.object_id = target.object_id
        AND source.object_name = target.object_name
        AND source.DatabaseName = target.DatabaseName
        AND source.SchemaName = target.SchemaName
        AND source.ObjectName = target.ObjectName
WHEN MATCHED THEN
    UPDATE SET
          ExecutionCount += source.execution_count
        , FirstExecutionTimestamp = CASE WHEN source.min_timestamp < target.FirstExecutionTimestamp THEN source.min_timestamp ELSE target.FirstExecutionTimestamp END
        , LastExecutionTimestamp = CASE WHEN source.max_timestamp > target.LastExecutionTimestamp THEN source.max_timestamp ELSE target.LastExecutionTimestamp END
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
          DatabaseName
        , SchemaName
        , ObjectName
        , source_database_id
        , object_id
        , object_name
        , ExecutionCount
        , FirstExecutionTimestamp
        , LastExecutionTimestamp
        )
    VALUES (
          source.DatabaseName
        , source.SchemaName
        , source.ObjectName
        , source.source_database_id
        , source.object_id
        , source.object_name
        , source.execution_count
        , source.min_timestamp
        , source.max_timestamp
        );
GO

以下是 PS 脚本示例.您需要修改系统的引用程序集路径(我使用的是安装了最新 SSMS 版本的程序集路径).

Below is the PS script example. You'll need to modify the referenced assembly paths for your system (I used the one's installed with the latest SSMS version).

Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XE.Core.dll"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XEvent.Linq.dll"

# utility class to summarize proc calls by source_database_id, object_id, and object_name
Add-Type -TypeDefinition `
@"
using System;
using System.Collections.Generic;
using System.Text;

namespace ExtendedEventsUtility
{
    public static class ExtendedEventsAggegator
    {

        public static Dictionary<string, ModuleEndSummary> AggregatedEvents = new Dictionary<string, ModuleEndSummary>();

        public static void AggregateTraceFiles(string xeFilePathPattern)
        {
            AggregatedEvents.Clear();
            using (var events = new Microsoft.SqlServer.XEvent.Linq.QueryableXEventData(xeFilePathPattern))
            {
                foreach (var xe in events)
                {
                    ExtendedEventsAggegator.aggregateEvent(xe);
                }
            }
        }

        private static void aggregateEvent(Microsoft.SqlServer.XEvent.Linq.PublishedEvent eventData)
        {
            ModuleEndSummary aggregatedEvent;
            var key = new StringBuilder();
            key.Append(eventData.Fields["source_database_id"].Value.ToString());
            key.Append("|");
            key.Append(eventData.Fields["object_id"].Value.ToString());
            key.Append("|");
            key.Append(eventData.Fields["object_name"].Value.ToString());
            var keyValue = key.ToString();
            if (AggregatedEvents.ContainsKey(keyValue))
            {
                aggregatedEvent = AggregatedEvents[keyValue];
            }
            else
            {
                aggregatedEvent = new ModuleEndSummary()
                {
                    source_database_id = (UInt32)eventData.Fields["source_database_id"].Value,
                    object_id = (Int32)eventData.Fields["object_id"].Value,
                    object_name = (string)eventData.Fields["object_name"].Value
                };
                AggregatedEvents.Add(keyValue, aggregatedEvent);
            }
            aggregatedEvent.executionCount += 1;
            if((DateTimeOffset)eventData.Timestamp < aggregatedEvent.minTimestamp)
            {
                aggregatedEvent.minTimestamp = (DateTimeOffset)eventData.Timestamp;
            }
            if ((DateTimeOffset)eventData.Timestamp > aggregatedEvent.maxTimestamp)
            {
                aggregatedEvent.maxTimestamp = (DateTimeOffset)eventData.Timestamp;
            }

        }

    }

    public class ModuleEndSummary
    {
        public UInt32 source_database_id;
        public Int32 object_id;
        public string object_name;
        public Int32 executionCount = 0;
        public DateTimeOffset minTimestamp = DateTimeOffset.MaxValue;
        public DateTimeOffset maxTimestamp = DateTimeOffset.MinValue;
    }
}
"@ -ReferencedAssemblies ("C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XE.Core.dll", "C:\Program Files\Microsoft SQL Server\140\Shared\Microsoft.SqlServer.XEvent.Linq.dll")

try {

    # move trace files that are not currently in use to import staging subfolder
    $sourceTraceFolderPath = "D:\SqlTraceFiles\"
    $targetTraceSubFolderPath = "D:\SqlTraceFiles\ImportStaging\"
    $traceFilePattern = "StoredProcedureExecutions*.xel"
    if(!(Test-Path $targetTraceSubFolderPath)) {
        [void](New-Item -Path $targetTraceSubFolderPath -ItemType Directory)
    }
    Get-Item "$sourceTraceFolderPath\$traceFilePattern" | Move-Item -Destination $targetTraceSubFolderPath -ErrorAction Ignore

    # aggegate usage by source_database_id, object_id, and object_name
    [ExtendedEventsUtility.ExtendedEventsAggegator]::AggregateTraceFiles("$targetTraceSubFolderPath\$traceFilePattern")

    # create data table for SqlBulkCopy
    $dt = New-Object System.Data.DataTable
    [void]$dt.Columns.Add("source_database_id", [System.Type]::GetType("System.Int16"))
    [void]$dt.Columns.Add("object_id", [System.Type]::GetType("System.Int32"))
    [void]$dt.Columns.Add("object_name", [System.Type]::GetType("System.String"))
    [void]$dt.Columns.Add("execution_count", [System.Type]::GetType("System.Int32"))
    [void]$dt.Columns.Add("min_timestamp", [System.Type]::GetType("System.DateTimeOffset"))
    [void]$dt.Columns.Add("max_timestamp", [System.Type]::GetType("System.DateTimeOffset"))

    # load proc execution summary into data table
    foreach ($proc in [ExtendedEventsUtility.ExtendedEventsAggegator]::AggregatedEvents.Values) {
        $row = $dt.NewRow()
        $dt.Rows.Add($row)
        $row["source_database_id"] = $proc.source_database_id
        $row["object_id"] = $proc.object_id
        $row["object_name"] = $proc.object_name
        $row["execution_count"] = $proc.executioncount
        $row["min_timestamp"] = $proc.mintimestamp
        $row["max_timestamp"] = $proc.maxtimestamp
    }

    # bulk insert execution summary into staging table
    $connectionString = "Data Source=.;Integrated Security=SSPI;Initial Catalog=YourDatabase"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand("TRUNCATE TABLE dbo.ModuleEndSummaryStaging;", $connection)
    $connection.Open()
    [void]$command.ExecuteNonQuery()
    $connection.Close()
    $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
    $bcp.DestinationTableName = "dbo.ModuleEndSummaryStaging"
    $bcp.WriteToServer($dt);
    $bcp.Dispose()

    # merge proc execution summary into history table
    $connection.Open()
    $command.CommandText="dbo.MergeStoredProcedureExecutionHistory"
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    [void]$command.ExecuteNonQuery()
    [void]$connection.Close()

    #delete files after import
    Get-ChildItem "$targetTraceSubFolderPath\$traceFilePattern" | Remove-Item

} catch {
    throw
}

这篇关于使用 sys.dm_exec_procedure_stats 识别未使用的过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！