Выявление неиспользуемых процедур с использованием sys.dm_exec_procedure_stats
У меня есть таблица, которая ежечасно обновлялась данными из sys.dm_exec_procedure_stats уже более года.
Если я не вижу процедуру, указанную в этой таблице, могу ли я быть уверен на 99%, что она больше не используется? Я имею в виду, я понимаю, что может быть какой-то безумный крайний случай, когда кто-то разработал процесс для запуска процесса, а затем немедленно удалил его из кэша, чтобы его использование никогда не регистрировалось моим процессом. Я явно готов игнорировать такие глупые сценарии.
1 ответ
Решение
sys.dm_exec_procedure_stats DMV отражает текущее состояние кеша процедур. SQL Server удаляет записи из этого DMV, когда соответствующая запись кэша удаляется, поэтому вы можете пропустить выполнение хранимых процедур, делая периодические снимки этого DMV. Также хранятся процы с RECOMPILE не будет захвачен.
Более надежный способ определения всех сохраненных процедур выполнения - это трассировка на стороне сервера, которая записывает в целевой файл. Затем данные трассировки могут быть обобщены и сохранены в таблицу.
Ниже приведен пример DDL для трассировки XE module_end события, поддержка объектов SQL и сценарий PowerShell. Сценарий PowerShell суммирует хранимые процедуры выполнения из файла (-ов) трассировки и сохраняет сводные данные в постоянной таблице для анализа. Сценарий PS может быть запланирован периодически для обработки прокручиваемых файлов трассировки.
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
}