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

五、细化到进程级别