SQL Server 从 【索引梳理】 到【查询性能调优】

-- 索引及主键信息
Select 
     TableId=O.[object_id],
     TableName=O.Name,
     IndexId=ISNULL(KC.[object_id],IDX.index_id),
     IndexName=IDX.Name,
     IndexType=ISNULL(KC.type_desc,'Index'),
     Index_Column_id=IDXC.index_column_id,
     ColumnID=C.Column_id,
     ColumnName=C.Name,
     Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
         WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
     PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
     [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,
     Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,
     Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,
     Fill_factor=IDX.fill_factor,
     Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END
 FROM sys.indexes IDX
     INNER JOIN sys.index_columns IDXC
         ON IDX.[object_id]=IDXC.[object_id]
             AND IDX.index_id=IDXC.index_id
     LEFT JOIN sys.key_constraints KC
         ON IDX.[object_id]=KC.[parent_object_id]
             AND IDX.index_id=KC.unique_index_id
     INNER JOIN sys.objects O
         ON O.[object_id]=IDX.[object_id]
     INNER JOIN sys.columns C
         ON O.[object_id]=C.[object_id]
             AND O.type='U'
             AND O.is_ms_shipped=0
             AND IDXC.Column_id=C.Column_id
 --    INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息
--    (
--        Select [object_id], Column_id, index_id=MIN(index_id)
 --        FROM sys.index_columns
 --        GROUP BY [object_id], Column_id
 --    ) IDXCUQ
 --        ON IDXC.[object_id]=IDXCUQ.[object_id]
 --            AND IDXC.Column_id=IDXCUQ.Column_id

 

查询出从上次SQL Server启动以来，所有没有被使用的索引

 

Select
         OBJECT_NAME(i.object_id) AS object_name,
         o.type_desc AS ojbect_type,
         i.name AS index_name,
         i.type_desc AS index_type
 FROM
         sys.indexes i INNER JOIN sys.objects o
                 ON i.object_id = o.object_id
 Where
         o.type_desc NOT IN ('INTERNAL_TABLE','SYSTEM_TABLE')
                 AND i.type_desc <> 'HEAP'
                 AND i.index_id NOT IN (Select s.index_id
                         FROM sys.dm_db_index_usage_stats s
                         Where s.object_id=i.object_id
                                 AND i.index_id=s.index_id
                                 AND database_id = DB_ID('QWorld') )
 orDER BY OBJECT_NAME(i.object_id), o.type_desc

 

查询出从上次SQL Server启动以来，被使用索引的统计信息

 

Select
         'ObjectName', 'OjbectType',
         'IndexId', 'UserSeeks', 'UserScans', 'UserLookups', 'UserUpdates',
         'LastUserSeek', 'LastUserScan', 'LastUserLookup', 'LastUserUpdate',
         'SystemSeeks', 'SystemScans', 'SystemLookups', 'SystemUpdates',
         'LastSystemSeek', 'LastSystemScan', 'LastSystemLookup', 'LastSystemUpdate'

 Select
         object_name(d.object_id) AS object_name,
         o.type_desc AS ojbect_type,
         index_id, user_seeks, user_scans, user_lookups, user_updates,
         last_user_seek, last_user_scan, last_user_lookup, last_user_update,
         system_seeks, system_scans, system_lookups, system_updates,
         last_system_seek, last_system_scan, last_system_lookup, last_system_update
 FROM
         sys.dm_db_index_usage_stats d INNER JOIN sys.objects o
                 ON d.object_id = o.object_id
 Where
         d.database_id = db_id('QWorld')
                 AND o.type_desc <> 'SYSTEM_TABLE'
 orDER BY
         object_name(d.object_id)

 

查询出索引锁片的统计情况

 

Select
         N'索引名', N'表名', N'架构名', N'索引类型', N'索引密度', N'索引碎片', N'页数'

Select
         i.name AS N'索引名',  
         o.name AS N'表名',  
         s.name AS N'架构名',  
         f.index_type_desc AS N'索引类型',  
         f.avg_page_space_used_in_percent AS N'索引密度',  
         f.avg_fragmentation_in_percent AS N'索引碎片',  
         f.page_count AS N'页数'  
FROM
         sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f  
                 INNER JOIN sys.objects o ON o.object_id = f.object_id  
                 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id   
                 INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id  
 Where
         page_count > 50 AND f.index_id > 0
 orDER BY
         --o.name, i.index_id
         f.avg_fragmentation_in_percent DESC;

 

 查询出索引统计信息更新时间

 

 

 

DECLARE @t table(
         tbl_name nvarchar(256),
         index_id int,
         index_name nvarchar(256),
         statistics_update_date datetime
 );

 Insert @t
 EXEC sp_MSforeachtable '
         Select
                 OBJECT_NAME(object_id),
                 index_id,
                 name AS index_name, 
                 STATS_DATE(object_id, index_id) AS statistics_update_date
         FROM
                 sys.indexes 
         Where
                 object_id = OBJECT_ID(''?'');';

 Select
         N'表名', N'索引ID', N'索引名', N'统计信息更新时间';

Select
         tbl_name AS N'表名',
         index_id AS N'索引ID',
         index_name AS N'索引名',
         statistics_update_date AS N'统计信息更新时间'
FROM
         @t
 orDER BY
         tbl_name;

 

Select d.name, OBJECT_NAME(object_id, database_id) 'proc name', 

 a.cached_time, a.last_execution_time,

 a.execution_count,
 a.execution_count/(DATEDIFF(ss,cached_time,last_execution_time)) as 'execution_count/s',

 total_worker_time,
 a.total_worker_time /a.execution_count avg_worker_time,
 max_worker_time,

 total_physical_reads,
 a.total_physical_reads /a.execution_count avg_physical_reads,
 max_physical_reads,

 total_logical_reads,
 a.total_logical_reads/ a.execution_count  avg_logical_reads,
 max_logical_reads,

 a.total_logical_writes,
 a.total_logical_writes/ a.execution_count  avg_logical_writes,
 max_logical_writes,

 a.total_elapsed_time,
 a.total_elapsed_time / a.execution_count   avg_elapsed_time,
 a.max_elapsed_time,

 b.text

 FROM sys.dm_exec_procedure_stats AS a

 CROSS APPLY sys.dm_exec_sql_text(a.sql_handle)  b

 CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

 LEFT JOIN master.sys.sysdatabases d on a.database_id=d.dbid

 Where DATEDIFF(ss,cached_time,last_execution_time)>3600
 and d.name='Lin2World'
 orDER BY (a.total_worker_time) DESC


 --select o.name,d.*
 --        , s.avg_total_user_cost
 --        , s.avg_user_impact
 --        , s.last_user_seek
 --        ,s.unique_compiles
 --from sys.dm_db_missing_index_group_stats s
 --        ,sys.dm_db_missing_index_groups g
 --        ,sys.dm_db_missing_index_details d
 --        ,sys.sysobjects o
 --        ,sys.sysdatabases db
 --where s.group_handle = g.index_group_handle
 --and d.index_handle = g.index_handle
 --and d.object_id=o.id
 --and d.database_id=db.dbid
 --and db.name='lin2world'
 --order by s.avg_user_impact desc
 --go

 

索引使用效率评估，使用很少的索引排在最先

 

Select
         N'对象名', N'对象ID', N'索引名', N'索引ID', N'seeks', N'scans', N'lookups', N'updates';

 Select 
         OBJECT_NAME(s.object_id) AS N'对象名',
         s.object_id AS N'对象ID',
         i.name AS N'索引名',
         i.index_id AS N'索引ID',
         user_seeks AS N'seeks',
         user_scans AS N'scans',
         user_lookups AS N'lookups',
         user_updates AS N'updates'  
 FROM
         sys.dm_db_index_usage_stats s,
         sys.indexes i  
 Where
         database_id = DB_ID() AND OBJECTPROPERTY(s.object_id,'isusertable') = 1  
                 AND i.object_id = s.object_id  
                 AND i.index_id = s.index_id  
 orDER BY
         (user_seeks + user_scans + user_lookups + user_updates) ASC;

 

经过对上面索引的一番梳理之后，我们该好好维护整理下了。

索引整理和重建、统计信息更新

 

-- replace DBName with your dbname
-- DBName 替换为 yourname

 use master
 go
 if OBJECTPROPERTY(OBJECT_ID('sp_indexdefragmentation'), 'IsProcedure') = 1
         drop procedure sp_indexdefragmentation
 go
 Create PROC [dbo].[sp_indexdefragmentation]
         @p_dbname SYSNAME = 'NL'
 AS
         BEGIN
         SET NOCOUNT ON

         IF @p_dbname = 'NL'
                 SET @p_dbname = DB_NAME();

         DECLARE
         @db_name SYSNAME,
         @tab_name SYSNAME,
         @ind_name VARCHAR(500),
         @schema_name SYSNAME,
         @frag FLOAT,
         @pages INT,
         @min_id INT,
         @max_id INT

         SET @db_name=@p_dbname

         --------------------------------------------------------------------------------------------------------------------------------------
         --inserting the Fragmentation details
         --------------------------------------------------------------------------------------------------------------------------------------
         Create TABLE #tempfrag
         (
         id INT IDENTITY,
         table_name SYSNAME,
         index_name VARCHAR(500),
         frag FLOAT,
         pages INT,
         schema_name SYSNAME
         )

         EXEC ('USE ['+@db_name+'];
         Insert INTO #tempfrag (table_name,index_name,frag,pages,schema_name)
         select
                 OBJECT_NAME(f.object_id) as obj
                 , i.name as ind
                 , f.avg_fragmentation_in_percent
                 , f.page_count
                 , TABLE_SCHEMA
         from sys.dm_db_index_physical_stats (DB_ID(), null, null, null, null) as f
                 inner join sys.indexes as i
                         on f.object_id = i.object_id 
                                 and i.index_id = f.index_id
                 inner join INFORMATION_SCHEMA.TABLES as s
                         on s.TABLE_NAME = OBJECT_NAME(f.object_id)
                                 and f.database_id = DB_ID()
                                 and OBJECTPROPERTY(i.object_id, ''ISSYSTEMTABLE'') = 0
         Where 
                 f.index_id > 0
                 and f.index_id <= 10000'        
         )

         Select @min_id=MIN(ID)FROM #tempfrag
         Select @max_id=MAX(ID)FROM #tempfrag

         -- TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

         WHILE (@min_id<=@max_id)
         BEGIN

         Select
         @tab_name=table_name,
         @schema_name=schema_name,
         @ind_name=index_name ,
         @frag=frag ,
         @pages=pages
         FROM #tempfrag Where id = @min_id

         --------------------------------------------------------------------------------------------------------------------------------------
         --Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
         --------------------------------------------------------------------------------------------------------------------------------------

         IF (@ind_name IS NOT NULL)
         BEGIN
         IF (@frag>=30 AND @pages>1000)
         BEGIN
         EXEC ('USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
                 --Insert INTO msdb.dbo.dba_defrag_maintenance_history
                 --VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
                 
                 PRINT 'USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD '
         END
         --------------------------------------------------------------------------------------------------------------------------------------
         --Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
         --------------------------------------------------------------------------------------------------------------------------------------
         ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )
         BEGIN
         BEGIN TRY
         EXEC ('USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
         EXEC ('USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
         
                 --Insert INTO msdb.dbo.dba_defrag_maintenance_history
                 --VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UpdateSTATS',GETDATE())

                 PRINT 'USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE '
                 PRINT 'USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) '

         
         END TRY
         BEGIN CATCH
         --------------------------------------------------------------------------------------------------------------------------------------
         --Check the fragmentation between 15% to 29% and pages greater than 1000 and page level 
         --lock disabled then rebuild
         --------------------------------------------------------------------------------------------------------------------------------------

         IF ERROR_NUMBER()=2552
         EXEC ('USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
                 --Insert INTO msdb.dbo.dba_defrag_maintenance_history
                 --VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',GETDATE())
                 PRINT 'USE ['+@db_name+'];Alter INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD '                
         END CATCH
         END

         --------------------------------------------------------------------------------------------------------------------------------------
         --Update the statistics for all indexes if the first three conditions is false
         --------------------------------------------------------------------------------------------------------------------------------------
         ELSE 
         BEGIN 
         EXEC ('USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' ) 
                 --Insert INTO msdb.dbo.dba_defrag_maintenance_history
                 --VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UpdateSTATS',GETDATE())                 
                 PRINT 'USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' 
         END 
         END 
         ELSE
         BEGIN

         --------------------------------------------------------------------------------------------------------------------------------------
         --Update the statistics for all tables if the first three conditions is false
         --------------------------------------------------------------------------------------------------------------------------------------
         EXEC ('USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+']') 
         --Insert INTO msdb.dbo.dba_defrag_maintenance_history 
         --VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UpdateSTATS',GETDATE()) 
                 PRINT 'USE ['+@db_name+'];Update STATISTICS ['+@schema_name+'].['+@tab_name+']';
         END

         SET @min_id=@min_id+1
         END
         Drop TABLE #tempfrag
         --Insert INTO master.dbo.dba_defrag_maintenance_history
         --Select * FROM msdb.dbo.dba_defrag_maintenance_history
         END
 go

 --execute
 use master

 exec dbo.[sp_indexdefragmentation]  DBName
 go

实际操作建议：

相信，这批脚本对大家的工作会有帮助。

大家可以根据顺序，先列出所有的索引和主键情况，根据后面列出的无效索引、缺失索引、索引效率统计等等和之前的完整信息作对比，逐个分析。根据实际的应用，再去权衡是否需要该索引，该索引是否建得得当，是否需要优化。当然逐个工作可以定期去做。接下来，就要做好自动化维护索引和统计信息的工作。将索引的重建和整理、统计信息的更新做到自动化定期执行。所要注意的是，要考量维护窗口的线上压力，对主键、聚集索引、非聚集索引做不同的策略。最后要注意的是对于在线索引重建，若存在LOB字段，直到SQL SERVER 2012版本才真正实现了。

如何重建索引及其性能影响：


重建索引是为了减少数据碎片。数据碎片会导致SQL Server进行不必要的数据读，降低SQL Server的性能。重建索引也会同时更新列统计，而如果查询所使用的列缺少或遗漏统计信息，这可能导致SQL Server内部的优化器选择比预期效率低的查询计划。


如果您重建了某张表上的聚集索引，该表上的非聚集索引也同时会被更新。



要更新索引，您可以使用Maintenance Wizard（相关内容您可以参考http://msdn.microsoft.com/en-us/library/ms180074.aspx），或在SQL Server代理（Agent）中运行如下的自定义代码来更新某个数据库中所有表上的索引：

您可以根据您的需求修改DBREINDEX的参数。

需要注意的是，重建非聚集索引时该表会暂时加上共享锁，对用户不可进行Select以外的操作；重建聚集索引时该表会暂时加上排外锁，不允许任何用户访问。因此需要制定好计划来预防可能的访问问题。



REBUILD有一个fill factor参数，如果fill factor设置为100%，这意味着每一个索引页都是完全满的，如果fill factor设置为50%意味着每个索引页都是半满的。对于fill factor 100%，每次新插入或更新一个记录，由于当前页没有空间可用，可能有分页情况产生。过多的分页会降低SQL Server的性能。下面具体举个例子：

假设您在一张表上建立了一个使用默认fill factor的新索引。当SQL Server创建索引时，它会把索引放置在连续的物理页上，以使数据顺序地被读，I/O访问最优化。但当表因Insert，Update，Delete等操作增长改变时，分页发生，SQL Server在磁盘的其他地方分配新的页，导致新的页与原物理页不连续，增加了随机I/O，访问索引页变慢。



那么fill factor的合适值应该为多少？这取决于表的读/写比：

    低更新表（读/写比：100比1）：100% fill factor

    高更新表（写超过读）：50%-70% fill factor

    居中：80%-90% fill factor



过低的fill factor会增加页的数量，也会导致更多的页需要被移至缓存，缓存中有用的数据减少。默认的fill factor为0（即100% fill factor），通常这不是个好的选择，特别是对于聚集索引。

如果您无法判断设置什么fill factor,您首先需要确定磁盘的读/写比.方法就是使用如下两个计数器:

Physical Disk Object: % Disk Read Time 和 Physical Disk Object: % Write Time。另外一个可能有用的计数器就是：SQL Server Access Methods: Pages Splits/Sec。这个计数器测量SQL Server内每秒分页的次数。如果该数值过高，您需要降低fill factor防止新的分页。



如果您想确认您的索引因分页产生的碎片程度，您可以运行DBCC SHOWCONTIG命令。如果看特定表和特定索引，您可以运行如下代码：

结果集中最重要的参数是Scan Density，越接近100%越好。如果Scan Density小于75%，那么您可能需要重建表中的索引。

对于小于100数据页，重建索引并不会有明显的性能改善。这是因为物理硬件缓存，SQL Server缓存和SQL Server预读机制隐藏了碎片的负面作用。但对于非常大的表，重建索引会使它受益匪浅，因为涉及大量磁盘I/O操作。

 

Rebuilding 和 Reorganizing 两者的区别：


Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

重新生成索引将会删除并重新创建索引。 这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 如果指定 ALL，将删除表中的所有索引，然后在单个事务中重新生成。

Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

重新组织索引使用最少系统资源重新组织索引。 通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。

Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

rebulid index既可以在online又可以在offline下执行，而reorganize index只能在online下执行的。

Difference between rebuild index online and offline：

既然rebuild index既可以是online模式，也可以是offline模式，那么两者有什么区别呢。这个我们可以参考stackoverflow上面的一篇文章：What is the difference between OFFLINE and ONLINE index rebuild in SQL Server?

在这里我还是简要总结一下：

online模式下

rebuild index会复制旧索引来新建索引，此时旧的索引依然可以被读取和修改，但是所以在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制，具体参考Online Index Operations 里面的Build Phase这一章节。然后在rebuild这个过程完整的时候，会对table上锁一段时间，在这段时间里会用新索引来替换旧索引，当这个过程完成以后再释放table上面的锁。如果索引列包含 LOB对象的话，在SQL Server 2005/2008/R2中rebuild index online会失败。在sql server 2012中，即使索引列包含LOB对象，也可以rebuild index online了，可以参考 Online Index Operations for indexes containing LOB columns.

offline模式下

rebuilde index会对table上锁，所有对这个table的读写操作都会被阻塞，在这期间新索引根据旧索引来创建，其实就是一个复制的过程，但是新索引没有碎片，最后使用新索引替换旧索引。当rebuild整个过程完成以后，table上面的锁才会被释放。