代码之家 › 专栏 › 技术社区 › BVernon

使用sys.dm_exec_procedure_stats识别未使用的过程

sql-server-2012

BVernon · 技术社区 · 6 年前

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

1 回复 | 直到 6 年前

Dan Guzman 6 年前

这个 sys.dm_exec_procedure_stats RECOMPILE 不会被抓获。

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

下面是XE跟踪的DDL示例 module_end

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版本一起安装的路径)。

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
}