sql server 2005 优化方法
一分析实例级的等待
--可能存在的性能问题
（）IO等待；
（）存储过程和代码的重新编译；
（）tempdb数据库页可能是瓶颈；
--1 不用重新启动数据库实例，重置'sys.dm_os_wait_stats'
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
--2 查询累计值达到系统等待时间%的重量级等待
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;
--3 找出高峰期
-- 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
--利用excel创建透视图分析
二联系等待和队列
--1 利用性能计数器监控cpu利用率，I/O队列，缓存命中率，内存计数器等。
--2 为服务器维护的每个性能计数器返回一行
SELECT
object_name,
counter_name,
instance_name,
cntr_value,
cntr_type
FROM sys.dm_os_performance_counters;
三确定方案
--根据以上分析掌握的信息，确定下一步方案
四细化到数据库/文件级别
--分析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;
五细化到进程级别
--1 注意事项：不要用SQL Server Profiler图形化跟踪；不要把跟踪数据写到数据库表；
不要把跟踪文件写到包含数据库文件的磁盘上；选择事件类和数据列，只跟踪需要的信息，
移除所有默认项和非必选项；列用筛选条件，如数据库ID；
--2 需要跟踪的事件类和字段
SP:Completed
SP:StmtCompleted
RPC:Completed
SQL:StmtCompleted
TextData Duration host application login
--3 用于创建跟踪的系统存储过程
--创建跟踪定义。新的跟踪将处于停止状态。
sp_trace_create
--创建用户定义事件
sp_trace_generateevent
--在跟踪中添加或删除事件或事件列。
sp_trace_setevent
--将筛选应用于跟踪。
sp_trace_setfilter
--修改指定跟踪的当前状态
--0 停止指定的跟踪。
--1 启动指定的跟踪。
--2 关闭指定的跟踪并从服务器中删除其定义。
sp_trace_setstatus
--以表格格式返回一或多个跟踪文件的内容
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:/temp/my_trace.trc', default);