Analyzing the Execution Plan
(1) 用SQL SERVER Profiler 捕获Query
note:将trace file convert to table or csv
http://msdn.microsoft.com/en-us/library/ms188425(SQL.90).aspx
USE AdventureWorks;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:/temp/my_trace.trc', default);
GO
(2) 分析IO 数据
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON
Displays the number of milliseconds required to parse, compile, and execute each statement
SET SHOWPLAN_ALL ON
(3) 分析哪个表的读最多
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 36 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(17049 row(s) affected)
Table 'tb'. Scan count 1, logical reads 68, physical reads 1, read-ahead reads 66, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(4) 如果logcal reads 太高(相比较得到的数据),则可以通过索引优化减少
4.1 对于外键,可以加个索引,对于比较大的表,可以加clustered index
4.2 clustered indexed 有吗?
4.3 clustered indexed 有被使用吗?在JOIN 或者 WHERE语句出现
4.4 有没有缺失的索引,或者能够建立索引覆盖
(5) 打开执行计划
分析 每个Query
(6) 分析Join 类型 , 试试 Join hints - loop, merge, hash