我最近一直在做一些SQL Server过程优化，并正在寻找一种测试模式（时间和结果明智）。到目前为止，我已经提供了这个解决方案：
SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------------
-- Procedures data and performance testing pattern
----------------------------------------------------------------------------------------------------------------
-- Prepare test queries (most likely will be taken from Logs.ProcedureTraceData (DATAUK/DATAUS servers)
-- Procedures should insert records into Temporary table, so we can compare their results using EXCEPT
-- If result set columns are fixed (i.e. no Dynamic SQL is used), we can create Temporary tables inside script
-- and insert records in them to do comparison and just TRUNCATE them at the end of the loop.
-- example here: http://stackoverflow.com/a/654418/3680098
-- If there're any data discrepancies or record counts are different, it will be displayed in TraceLog table
----------------------------------------------------------------------------------------------------------------
-- Create your own TraceLog table to keep records
----------------------------------------------------------------------------------------------------------------
/*
CREATE TABLE Temporary._EB_TraceLog
(
ID INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_Temporary_EB_TraceLog_ID PRIMARY KEY
, CurrentExecutionTime INT
, TempExecutionTime INT
, CurrentExecutionResultsCount INT
, TempExecutionResultsCount INT
, IsDifferent BIT CONSTRAINT DF_Temporary_EB_TraceLog_IsDifferent DEFAULT 0 NOT NULL
, TimeDiff AS CurrentExecutionTime - TempExecutionTime
, PercentageDiff AS CAST(((CAST(CurrentExecutionTime AS DECIMAL)/ CAST(TempExecutionTime AS DECIMAL)) * 100 - 100) AS DECIMAL(10, 2))
, TextData NVARCHAR(MAX)
);
SELECT *
FROM Temporary._EB_TraceLog;
TRUNCATE TABLE Temporary._EB_TraceLog;
*/
INSERT INTO Temporary._EB_TraceLog (TextData)
SELECT TextData
FROM Temporary._EB_GetData_Timeouts
EXCEPT
SELECT TextData
FROM Temporary._EB_TraceLog;
DECLARE @Counter INT;
SELECT @Counter = MIN(ID)
FROM Temporary._EB_TraceLog
WHERE CurrentExecutionTime IS NULL
OR TempExecutionTime IS NULL
OR CurrentExecutionResultsCount IS NULL
OR TempExecutionResultsCount IS NULL;
WHILE @Counter <= (SELECT MAX(ID) FROM Temporary._EB_TraceLog)
BEGIN
DECLARE @SQLStringCurr NVARCHAR(MAX);
DECLARE @SQLStringTemp NVARCHAR(MAX);
DECLARE @StartTime DATETIME2;
SELECT @SQLStringCurr = REPLACE(TextData, 'dbo.GetData', 'Temporary._EB_GetData_Orig')
, @SQLStringTemp = REPLACE(TextData, 'dbo.GetData', 'Temporary._EB_GetData_Mod')
FROM Temporary._EB_TraceLog
WHERE ID = @Counter;
----------------------------------------------------------------------------------------------------------------
-- Drop temporary tables in script, so these numbers don't figure in SP execution time
----------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'Temporary._EB_Test_Orig') IS NOT NULL
DROP TABLE Temporary._EB_Test_Orig;
IF OBJECT_ID(N'Temporary._EB_Test_Mod') IS NOT NULL
DROP TABLE Temporary._EB_Test_Mod;
----------------------------------------------------------------------------------------------------------------
-- Actual testing
----------------------------------------------------------------------------------------------------------------
-- Take time snapshot and execute original procedure, which inserts records into Temporary table
-- When done - measurements will be updated on TraceLog table
----------------------------------------------------------------------------------------------------------------
SELECT @StartTime = CURRENT_TIMESTAMP;
EXECUTE sp_executesql @SQLStringCurr;
UPDATE T
SET T.CurrentExecutionTime = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP)
FROM Temporary._EB_TraceLog AS T
WHERE T.ID = @Counter;
----------------------------------------------------------------------------------------------------------------
-- Take time snapshot and execute optimized procedure, which inserts records into Temporary table
-- When done - measurements will be updated on TraceLog table
----------------------------------------------------------------------------------------------------------------
SELECT @StartTime = CURRENT_TIMESTAMP;
EXECUTE sp_executesql @SQLStringTemp;
UPDATE T
SET T.TempExecutionTime = DATEDIFF(MILLISECOND, @StartTime, CURRENT_TIMESTAMP)
FROM Temporary._EB_TraceLog AS T
WHERE T.ID = @Counter;
----------------------------------------------------------------------------------------------------------------
-- Check if there are any data discrepancies
-- If there are any, set IsDifferent to 1, so we can find the root cause
----------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM Temporary._EB_Test_Mod EXCEPT SELECT * FROM Temporary._EB_Test_Orig)
OR EXISTS (SELECT * FROM Temporary._EB_Test_Orig EXCEPT SELECT * FROM Temporary._EB_Test_Mod)
BEGIN
UPDATE T
SET T.IsDifferent = 1
FROM Temporary._EB_TraceLog AS T
WHERE T.ID = @Counter;
END
----------------------------------------------------------------------------------------------------------------
-- Update record counts for each execution
-- We can check if there aren't any different record counts even tho results are same
-- EXCEPT clause removes duplicates when doing checks
----------------------------------------------------------------------------------------------------------------
UPDATE T
SET T.CurrentExecutionResultsCount = (SELECT COUNT(*) FROM Temporary._EB_Test_Orig)
, T.TempExecutionResultsCount = (SELECT COUNT(*) FROM Temporary._EB_Test_Mod)
FROM Temporary._EB_TraceLog AS T
WHERE T.ID = @Counter;
----------------------------------------------------------------------------------------------------------------
-- Print iteration number and proceed on next one
----------------------------------------------------------------------------------------------------------------
PRINT @Counter;
SET @Counter += 1;
END
SELECT *
FROM Temporary._EB_TraceLog;
到目前为止，这运行良好，但我想在每次迭代中包含 IO 和 TIME 统计信息。这可能吗？
我知道我可以使用：
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
但是有没有办法抓取汇总的值并将它们放在我的 TraceLog 表中？
最重要的是，这段代码中有什么没有意义的吗？
谢谢
您可以使用此查询
SELECT total_elapsed_time
FROM sys.dm_exec_query_stats
WHERE sql_handle in (SELECT most_recent_sql_handle
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE session_id = (@@spid))