优化查询的几点建议之三

以下几点建议：

1，检查是否遗漏查询表的JOIN;

2，检查是否遗漏查询表的WHERE;

3，检查统计是否自动创建和更新；

4，检查统计是否保持最新；

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);
GO

Exec sp_updatestats

UPDATE STATISTICS Person.Address WITH FULLSCAN

5，检查表和索引扫描；

SELECT avg_total_user_cost,avg_user_impact,user_seeks, user_scans,
ID.equality_columns,ID.inequality_columns,ID.included_columns,ID.statement 
FROM sys.dm_db_missing_index_group_stats GS
LEFT OUTER JOIN sys.dm_db_missing_index_groups IG On (IG.index_group_handle = GS.group_handle)
LEFT OUTER JOIN sys.dm_db_missing_index_details ID On (ID.index_handle = IG.index_handle)
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

6，检查RID LOOKUP；

7，检查排序；

如果排序在执行计划占很高比例，考虑：

增加CLUSTER INDEX在排序列；

使用索引视图；

增加NO CLUSTER INDEX，包括所有返回列。

8，检查是否索引碎片过大；

超过30%，考虑重建或重组索引。

Declare @db	SysName;
Set @db = '<DB NAME>';

SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name',
 CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type',
 I.Name As 'Index Name',
 avg_fragmentation_in_percent As 'Avg % Fragmentation',
 record_count As 'RecordCount',
 page_count As 'Pages Allocated',
 avg_page_space_used_in_percent As 'Avg % Page Space Used'
FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) S
LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)
AND S.INDEX_ID > 0
ORDER BY avg_fragmentation_in_percent DESC

ALTER INDEX ALL ON <Table Name> REBUILD（OR REORGANIZE);

ALTER INDEX <Index Name> ON <Table Name> REBUILD(OR REORGANIZE);

9，检查是否有表锁；

事物短为好；

考虑并行，使用'Read Committed using row versioning' or 'Snapshot'；

明确表暗示，使用“READUNCOMMITED OR READPAST".