sqlserver2005:T-Sql查询之物理查询优化
一、优化方法论
1.分析实例级的等待
2.联系等待和队列
3.确定方案
4.细化到数据库/文件级
5.细化到进程级
6.优化索引/查询
二、分析实例级的等待
在SQL SERVER 2005中,通过查询动态管理视图(DMV) sys.dm_os_wait_stats可以找出那些等待类型占用了大部分的等待时间,而在SQL SERVER 2000中,则通过运行DBCC SQLPERF(WAITSTATS)命令来完成。这个DMV从服务器最后一次重新启动开始累积值,若果你想重置它的值,运行下面的代码:DBCC SQLPERF('sys.dm_os_wait_stats',clear),在SQL SERVER 2000中下面的代码将重置等待统计:DBCC SQLPERF(waitstats,clear)。
重量级等待(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 --v percentage threshold ORDER BY W1.rn;
-- 为了分析重量级等待,将等待信息记录下来
-- 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 alter 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 >= '2010-07-10 23:24:46.783' AND dt < '2010-07-12 23:24:46.783' ) 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 <= '2010-07-13 23:24:46.783' ; 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('2010-07-09 23:24:46.783', '2010-07-13 23:24:46.783') AS F; 三、联系等待和队列
-- 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
四、细化到数据库/文件级别
在数据库/文件级别分析I/O信息的工具是动态管理函数sys.dm_io_virtual_file_stats该函数接受一个数据库ID和文件ID作为输入,返回与该数据库文件相关的I/O信息,为
这两个参数指定NULL会返回所有数据库和所有文件的信息。
-- 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五、细化到进程级别