SQL Server最佳化方法、步驟和技術

使用自頂向下的分析方法，透過分析重要的例項級等待(waits)，找出問題領域，透過細化，找出最值得最佳化的程式。最後透過調整索引和查詢，來提高程式效能。

必要時，也可以每半小時或一小時蒐集一次等待資訊，分析一天中等待的分佈，找出高峰期。

[@more@]

步驟：
1. 分析例項級的等待；
2. 聯絡等待和佇列；
3. 確定方案；
4. 細化到資料庫和檔案級；
5. 細化到程式級；
6. 最佳化索引和查詢；

在分析過程中，對不同型別的系統，要找出問題領域(problem area)。
1. I/O等待是最常見的等待；對查詢或索引設計或最佳化不理想時，常常造成額外的I/O；當然儲存規劃一開始就要做好，例如把Log檔案放在最快的磁碟陣列上，把tempdb放在單獨的磁碟驅動器上；
2. OLTP系統通常是透過儲存過程或查詢只訪問少量資料，但呼叫非常頻繁。一般程式碼的編譯和重新編譯可能會成為產生瓶頸的主要原因；事務日誌也會成為瓶頸；
3. tempdb也會成為一個非常嚴重的瓶頸；

---------------------------------------------------------------------
-- Analyze Waits at the Instance Level
---------------------------------------------------------------------

-- SQL Server 2005
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type;

-- SQL Server 2000
DBCC SQLPERF(WAITSTATS);

-- Isolate top waits
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
-- filter out additional irrelevant waits
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
ORDER BY W1.rn;
GO

-- Create the WaitStats table
USE Performance;
GO
IF OBJECT_ID('dbo.WaitStats') IS NOT NULL
DROP TABLE dbo.WaitStats;
GO

SELECT GETDATE() AS dt,
wait_type, waiting_tasks_count, wait_time_ms,
max_wait_time_ms, signal_wait_time_ms
INTO dbo.WaitStats
FROM sys.dm_os_wait_stats
WHERE 1 = 2;

ALTER TABLE dbo.WaitStats
ADD CONSTRAINT PK_WaitStats PRIMARY KEY(dt, wait_type);
CREATE INDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);
GO

-- Load waitstats data on regular intervals
INSERT INTO Performance.dbo.WaitStats
SELECT GETDATE(),
wait_type, waiting_tasks_count, wait_time_ms,
max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;
GO

-- Creation script for fn_interval_waits function
IF OBJECT_ID('dbo.fn_interval_waits') IS NOT NULL
DROP FUNCTION dbo.fn_interval_waits;
GO

CREATE FUNCTION dbo.fn_interval_waits
(@fromdt AS DATETIME, @todt AS DATETIME)
RETURNS TABLE
AS

RETURN
WITH Waits AS
(
SELECT dt, wait_type, wait_time_ms,
ROW_NUMBER() OVER(PARTITION BY wait_type
ORDER BY dt) AS rn
FROM dbo.WaitStats
WHERE dt >= @fromdt
AND dt < @todt + 1
)
SELECT Prv.wait_type, Prv.dt AS start_time,
CAST((Cur.wait_time_ms - Prv.wait_time_ms)
/ 1000. AS DECIMAL(12, 2)) AS interval_wait_s
FROM Waits AS Cur
JOIN Waits AS Prv
ON Cur.wait_type = Prv.wait_type
AND Cur.rn = Prv.rn + 1
AND Prv.dt <= @todt;
GO

-- Return interval waits
SELECT wait_type, start_time, interval_wait_s
FROM dbo.fn_interval_waits('20060212', '20060215') AS F
ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC,
wait_type, start_time;
GO

-- Prepare view for pivot table
IF OBJECT_ID('dbo.VIntervalWaits') IS NOT NULL
DROP VIEW dbo.VIntervalWaits;
GO

CREATE VIEW dbo.VIntervalWaits
AS

SELECT wait_type, start_time, interval_wait_s
FROM dbo.fn_interval_waits('20060212', '20060215') AS F;
GO

---------------------------------------------------------------------
-- Correlate Waits with Queues
---------------------------------------------------------------------

-- SQL Server 2005
SELECT
object_name,
counter_name,
instance_name,
cntr_value,
cntr_type
FROM sys.dm_os_performance_counters;

-- SQL Server 2000
SELECT
object_name,
counter_name,
instance_name,
cntr_value,
cntr_type
FROM master.dbo.sysperfinfo;
GO

---------------------------------------------------------------------
-- Determine Course of Action
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Drill Down to the Database/File Level
---------------------------------------------------------------------

-- SQL Server 2005

-- Analyze DB IO
WITH DBIO AS
(
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
)
SELECT db, file_type,
CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
CAST(100. * io_stall / SUM(io_stall) OVER()
AS DECIMAL(10, 2)) AS io_stall_pct,
ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;

-- SQL Server 2000
SELECT * FROM ::fn_virtualfilestats(15, 1);
GO

---------------------------------------------------------------------
-- Drill Down to the Process Level
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Trace Performance Workload
---------------------------------------------------------------------

-- Listing 3-3: Creation Script for the sp_perfworkload_trace_start Stored Procedure
SET NOCOUNT ON;
USE master;
GO

IF OBJECT_ID('dbo.sp_perfworkload_trace_start') IS NOT NULL
DROP PROC dbo.sp_perfworkload_trace_start;
GO

CREATE PROC dbo.sp_perfworkload_trace_start
@dbid AS INT,
@tracefile AS NVARCHAR(254),
@traceid AS INT OUTPUT
AS

-- Create a Queue
DECLARE @rc AS INT;
DECLARE @maxfilesize AS BIGINT;

SET @maxfilesize = 5;

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL
IF (@rc != 0) GOTO error;

-- Client side File and Table cannot be scripted

-- Set the events
DECLARE @on AS BIT;
SET @on = 1;
EXEC sp_trace_setevent @traceid, 10, 15, @on;
EXEC sp_trace_setevent @traceid, 10, 8, @on;
EXEC sp_trace_setevent @traceid, 10, 16, @on;
EXEC sp_trace_setevent @traceid, 10, 48, @on;
EXEC sp_trace_setevent @traceid, 10, 1, @on;
EXEC sp_trace_setevent @traceid, 10, 17, @on;
EXEC sp_trace_setevent @traceid, 10, 10, @on;
EXEC sp_trace_setevent @traceid, 10, 18, @on;
EXEC sp_trace_setevent @traceid, 10, 11, @on;
EXEC sp_trace_setevent @traceid, 10, 12, @on;
EXEC sp_trace_setevent @traceid, 10, 13, @on;
EXEC sp_trace_setevent @traceid, 10, 14, @on;
EXEC sp_trace_setevent @traceid, 45, 8, @on;
EXEC sp_trace_setevent @traceid, 45, 16, @on;
EXEC sp_trace_setevent @traceid, 45, 48, @on;
EXEC sp_trace_setevent @traceid, 45, 1, @on;
EXEC sp_trace_setevent @traceid, 45, 17, @on;
EXEC sp_trace_setevent @traceid, 45, 10, @on;
EXEC sp_trace_setevent @traceid, 45, 18, @on;
EXEC sp_trace_setevent @traceid, 45, 11, @on;
EXEC sp_trace_setevent @traceid, 45, 12, @on;
EXEC sp_trace_setevent @traceid, 45, 13, @on;
EXEC sp_trace_setevent @traceid, 45, 14, @on;
EXEC sp_trace_setevent @traceid, 45, 15, @on;
EXEC sp_trace_setevent @traceid, 41, 15, @on;
EXEC sp_trace_setevent @traceid, 41, 8, @on;
EXEC sp_trace_setevent @traceid, 41, 16, @on;
EXEC sp_trace_setevent @traceid, 41, 48, @on;
EXEC sp_trace_setevent @traceid, 41, 1, @on;
EXEC sp_trace_setevent @traceid, 41, 17, @on;
EXEC sp_trace_setevent @traceid, 41, 10, @on;
EXEC sp_trace_setevent @traceid, 41, 18, @on;
EXEC sp_trace_setevent @traceid, 41, 11, @on;
EXEC sp_trace_setevent @traceid, 41, 12, @on;
EXEC sp_trace_setevent @traceid, 41, 13, @on;
EXEC sp_trace_setevent @traceid, 41, 14, @on;

-- Set the Filters
DECLARE @intfilter AS INT;
DECLARE @bigintfilter AS BIGINT;

-- Application name filter
EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';
-- Database ID filter
EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

-- Set the trace status to start
EXEC sp_trace_setstatus @traceid, 1;

-- Print trace id and file name for future references
PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))
+ ', Trace File: ''' + @tracefile + '.trc''';

GOTO finish;

error:
PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish:
GO

-- Start the trace
DECLARE @dbid AS INT, @traceid AS INT;
SET @dbid = DB_ID('Performance');

EXEC dbo.sp_perfworkload_trace_start
@dbid = @dbid,
@tracefile = 'c:tempPerfworkload 20060828',
@traceid = @traceid OUTPUT;
GO

-- Stop the trace (assuming trace id was 2)
EXEC sp_trace_setstatus 2, 0;
EXEC sp_trace_setstatus 2, 2;
GO

---------------------------------------------------------------------
-- Analyze Trace Data
---------------------------------------------------------------------

-- Load trace data to table
SET NOCOUNT ON;
USE Performance;
GO
IF OBJECT_ID('dbo.Workload') IS NOT NULL
DROP TABLE dbo.Workload;
GO

SELECT CAST(TextData AS NVARCHAR(MAX)) AS tsql_code,
Duration AS duration
INTO dbo.Workload
FROM sys.fn_trace_gettable('c:tempPerfworkload 20060828.trc', NULL) AS T
WHERE Duration IS NOT NULL;
GO

-- Aggregate trace data by query
SELECT
tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY tsql_code;

-- Aggregate trace data by query prefix
SELECT
SUBSTRING(tsql_code, 1, 100) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 100);

-- Adjust substring length
SELECT
SUBSTRING(tsql_code, 1, 94) AS tsql_code,
SUM(duration) AS total_duration
FROM dbo.Workload
GROUP BY SUBSTRING(tsql_code, 1, 94);

-- Query Signature

-- Query template
DECLARE @my_templatetext AS NVARCHAR(MAX);
DECLARE @my_parameters AS NVARCHAR(MAX);

EXEC sp_get_query_template
N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78',
@my_templatetext OUTPUT,
@my_parameters OUTPUT;

SELECT @my_templatetext AS querysig, @my_parameters AS params;
GO

-- Listing 3-4: Creation Script for the fn_SQLSigTSQL UDF
IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigTSQL;
GO

CREATE FUNCTION dbo.fn_SQLSigTSQL
(@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)

--
-- This function is provided "AS IS" with no warranties,
-- and confers no rights.
-- Use of included script samples are subject to the terms specified at
--
--
-- Strips query strings
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @mode AS CHAR(10);
DECLARE @maxlength AS INT;
DECLARE @p2 AS NCHAR(4000);
DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);
DECLARE @p2len AS INT;

SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));
SET @maxlength = CASE WHEN @maxlength > @parselength
THEN @parselength ELSE @maxlength END;
SET @pos = 1;
SET @p2 = '';
SET @p2len = 0;
SET @currchar = '';
set @nextchar = '';
SET @mode = 'command';

WHILE (@pos <= @maxlength)
BEGIN
SET @currchar = SUBSTRING(@p1,@pos,1);
SET @nextchar = SUBSTRING(@p1,@pos+1,1);
IF @mode = 'command'
BEGIN
SET @p2 = LEFT(@p2,@p2len) + @currchar;
SET @p2len = @p2len + 1 ;
IF @currchar IN (',','(',' ','=','','!')
AND @nextchar BETWEEN '0' AND '9'
BEGIN
SET @mode = 'number';
SET @p2 = LEFT(@p2,@p2len) + '#';
SET @p2len = @p2len + 1;
END
IF @currchar = ''''
BEGIN
SET @mode = 'literal';
SET @p2 = LEFT(@p2,@p2len) + '#''';
SET @p2len = @p2len + 2;
END
END
ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','','!')
SET @mode= 'command';
ELSE IF @mode = 'literal' AND @currchar = ''''
SET @mode= 'command';

SET @pos = @pos + 1;
END
RETURN @p2;
END
GO

-- Test fn_SQLSigTSQL Function
SELECT dbo.fn_SQLSigTSQL
(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);
GO

-- Listing 3-5: fn_SQLSigCLR and fn_RegexReplace Functions, C# Version
/*
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class SQLSignature
{
// fn_SQLSigCLR
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_SQLSigCLR(SqlString querystring)
{
return (SqlString)Regex.Replace(
querystring.Value,
@"([s,(=<>!](?![^]]+[]]))(?:(?:(?:(?# expression coming
)(?:([N])?(')(?:[^']|'')*('))(?# character
)|(?:0x[da-fA-F]*)(?# binary
)|(?:[-+]?(?:(?:[d]*.[d]*|[d]+)(?# precise number
)(?:[eE]?[d]*)))(?# imprecise number
)|(?:[~]?[-+]?(?:[d]+))(?# integer
))(?:[s]?[+-*/%&|^][s]?)?)+(?# operators
))",
@"$1$2$3#$4");
}

// fn_RegexReplace - for generic use of RegEx-based replace
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString fn_RegexReplace(
SqlString input, SqlString pattern, SqlString replacement)
{
return (SqlString)Regex.Replace(
input.Value, pattern.Value, replacement.Value);
}
}
*/

-- Enable CLR
EXEC sp_configure 'clr enable', 1;
RECONFIGURE;
GO

-- Create assembly
USE Performance;
CREATE ASSEMBLY SQLSignature
FROM 'C:SQLSignatureSQLSignaturebinDebugSQLSignature.dll';
GO

-- Create fn_SQLSigCLR and fn_RegexReplace functions
CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_SQLSigCLR;
GO

CREATE FUNCTION dbo.fn_RegexReplace(
@input AS NVARCHAR(MAX),
@pattern AS NVARCHAR(MAX),
@replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLSignature.SQLSignature.fn_RegexReplace;
GO

-- Return trace data with query signature
SELECT
dbo.fn_SQLSigCLR(tsql_code) AS sig,
duration
FROM dbo.Workload;

SELECT
dbo.fn_RegexReplace(tsql_code,
N'([s,(=<>!](?![^]]+[]]))(?:(?:(?:(?# expression coming
)(?:([N])?('')(?:[^'']|'''')*(''))(?# character
)|(?:0x[da-fA-F]*)(?# binary
)|(?:[-+]?(?:(?:[d]*.[d]*|[d]+)(?# precise number
)(?:[eE]?[d]*)))(?# imprecise number
)|(?:[~]?[-+]?(?:[d]+))(?# integer
))(?:[s]?[+-*/%&|^][s]?)?)+(?# operators
))',
N'$1$2$3#$4') AS sig,
duration
FROM dbo.Workload;

-- Return trace data with query signature checksum
SELECT
CHECKSUM(dbo.fn_SQLSigCLR(tsql_code)) AS cs,
duration
FROM dbo.Workload;
GO

-- Add cs column to Workload table
ALTER TABLE dbo.Workload ADD cs INT NOT NULL DEFAULT (0);
GO
UPDATE dbo.Workload
SET cs = CHECKSUM(dbo.fn_SQLSigCLR(tsql_code));

CREATE CLUSTERED INDEX idx_cl_cs ON dbo.Workload(cs);
GO

-- Query Workload
SELECT tsql_code, duration, cs
FROM dbo.Workload;
GO

-- Aggregate data by query signature checksum

-- Load aggregate data into temporary table
IF OBJECT_ID('tempdb..#AggQueries') IS NOT NULL
DROP TABLE #AggQueries;
GO

SELECT cs, SUM(duration) AS total_duration,
100. * SUM(duration) / SUM(SUM(duration)) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY SUM(duration) DESC) AS rn
INTO #AggQueries
FROM dbo.Workload
GROUP BY cs;

CREATE CLUSTERED INDEX idx_cl_cs ON #AggQueries(cs);
GO

-- Show aggregate data
SELECT cs, total_duration, pct, rn
FROM #AggQueries
ORDER BY rn;

-- Show running totals
SELECT AQ1.cs,
CAST(AQ1.total_duration / 1000.
AS DECIMAL(12, 2)) AS total_s,
CAST(SUM(AQ2.total_duration) / 1000.
AS DECIMAL(12, 2)) AS running_total_s,
CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct,
AQ1.rn
FROM #AggQueries AS AQ1
JOIN #AggQueries AS AQ2
ON AQ2.rn <= AQ1.rn
GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn
HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold
-- OR AQ1.rn <= 5
ORDER BY AQ1.rn;

-- Isolate top offenders
WITH RunningTotals AS
(
SELECT AQ1.cs,
CAST(AQ1.total_duration / 1000.
AS DECIMAL(12, 2)) AS total_s,
CAST(SUM(AQ2.total_duration) / 1000.
AS DECIMAL(12, 2)) AS running_total_s,
CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct,
AQ1.rn
FROM #AggQueries AS AQ1
JOIN #AggQueries AS AQ2
ON AQ2.rn <= AQ1.rn
GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn
HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold
-- OR AQ1.rn <= 5
)
SELECT RT.rn, RT.pct, W.tsql_code
FROM RunningTotals AS RT
JOIN dbo.Workload AS W
ON W.cs = RT.cs
ORDER BY RT.rn;

-- Isolate sig of top offenders and a sample query of each sig
WITH RunningTotals AS
(
SELECT AQ1.cs,
CAST(AQ1.total_duration / 1000.
AS DECIMAL(12, 2)) AS total_s,
CAST(SUM(AQ2.total_duration) / 1000.
AS DECIMAL(12, 2)) AS running_total_s,
CAST(AQ1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(AQ2.pct) AS DECIMAL(12, 2)) AS run_pct,
AQ1.rn
FROM #AggQueries AS AQ1
JOIN #AggQueries AS AQ2
ON AQ2.rn <= AQ1.rn
GROUP BY AQ1.cs, AQ1.total_duration, AQ1.pct, AQ1.rn
HAVING SUM(AQ2.pct) - AQ1.pct <= 90 -- percentage threshold
)
SELECT RT.rn, RT.pct, S.sig, S.tsql_code AS sample_query
FROM RunningTotals AS RT
CROSS APPLY
(SELECT TOP(1) tsql_code, dbo.fn_SQLSigCLR(tsql_code) AS sig
FROM dbo.Workload AS W
WHERE W.cs = RT.cs) AS S
ORDER BY RT.rn;
GO