SQL Server优化常用命令

• 对执行sql进行分析。打开SQL Server的查询分析器。在下面语句两个go中间加入要执行的sql语句。 

set statistics profile on
set statistics io on --显示磁盘活动信息
set statistics time on --显示执行时间
go
<这里写上你的语句...>
go
set statistics profile off
set statistics io off
set statistics time off

• 索引的建立

--建聚集索引

CREATE CLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

--建非聚集索引

CREATE NONCLUSTERED INDEX INDEX_Userid  ON T_UserInfo (Userid)

--删除索引

DROP INDEX T_UserInfo.INDEX_Userid

 

• 查看是否发生了索引碎片，ExternalFragmentation的值>10表示对应的索引发生了外部碎片，InternalFragmentation的值<75表示对应的索引发生了内部碎片。

 

SELECT object_name(dt.object_id) Tablename,si.name

IndexName,dt.avg_fragmentation_in_percent AS

ExternalFragmentation,dt.avg_page_space_used_in_percent AS

InternalFragmentation

FROM

(

SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'

)

WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id

AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10

AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC

 

 

 

1)重组有碎片的索引：执行下面的命令

ALTER INDEX ALL ON TableName REORGANIZE

2)重建索引：执行下面的命令

ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

当对应索引的外部碎片值介于10-15之间，内部碎片值介于60-75之间时使用重组，其它情况就应该使用重建。