----------------------------------------------------------------------------------------------
-------------------------------------- 笔者的初衷 -------------------------------------
----------------------------------------------------------------------------------------------
首先,笔者将从实际操作入手,介绍DBA在日常运维过程中,如何利用SQL语句去对线上业务数据库的索引进行梳理。
然后,开始认识索引,介绍索引的作用,索引的分类,索引的结构。
接着,介绍索引的选择,索引的创建,利用索引进行查询性能的优化。
最后,进行总结。
----------------------------------------------------------------------------------------------
------------------------------------- 华丽的分割线 ---------------------------------------
先给出几个索引梳理的常用脚本。
查询出所有的索引及主键信息
-- 索引及主键信息
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;
查询SP执行效率和可能未建立索引表
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下执行的。