索引分析

索引分析

1.索引方法论

索引方法论包含3个步骤：监控分析和实施。这三步是循环运行的，先监控后分析最后实施，然后在监控在分析在实施，知道没有明显的和潜在的索引问题为止。

2.监控

常见的监控工具有下面的3种：

1.性能监视器

2.动态管理对象

3.SQL Trace

IF DB_ID(N'IndexDemo') IS NOT NULL DROP DATABASE IndexDemoCREATE DATABASE IndexDemogo--创建一个表，用于存放对应计数器的值：USE IndexDemoGOIF OBJECT_ID(N'IndexingCounters', 'U') IS NOT NULL DROP TABLE IndexingCountersCREATE TABLE IndexingCounters ( counter_id INT IDENTITY(1, 1) , create_date DATETIME , server_name VARCHAR(128) NOT NULL , object_name VARCHAR(128) NOT NULL , counter_name VARCHAR(128) NOT NULL , instance_name VARCHAR(128) NULL , Calculated_Counter_value FLOAT NULL , CONSTRAINT PK_IndexingCounters PRIMARY KEY CLUSTERED ( counter_id ) )GOCREATE NONCLUSTERED INDEX IX_IndexingCounters_CounterNameON dbo.IndexingCounters (counter_name)INCLUDE (create_date,server_name,object_name,Calculated_Counter_value)

对于性能计数器，可以很频繁的查询，但是如果是监控索引相关的情况，则没必要太过密集，可以每5分钟执行 一次

IF DB_ID(N'IndexDemo') IS NOT NULL DROP DATABASE IndexDemoCREATE DATABASE IndexDemogo--创建一个表，用于存放对应计数器的值：USE IndexDemoGOIF OBJECT_ID(N'IndexingCounters', 'U') IS NOT NULL DROP TABLE IndexingCountersCREATE TABLE IndexingCounters ( counter_id INT IDENTITY(1, 1) , create_date DATETIME , server_name VARCHAR(128) NOT NULL , object_name VARCHAR(128) NOT NULL , counter_name VARCHAR(128) NOT NULL , instance_name VARCHAR(128) NULL , Calculated_Counter_value FLOAT NULL , CONSTRAINT PK_IndexingCounters PRIMARY KEY CLUSTERED ( counter_id ) )GOCREATE NONCLUSTERED INDEX IX_IndexingCounters_CounterNameON dbo.IndexingCounters (counter_name)INCLUDE (create_date,server_name,object_name,Calculated_Counter_value)USE IndexDemoIF OBJECT_ID('tempdb..#Delta') IS NOT NULLDROP TABLE #DeltaSELECT opc.object_name,opc.counter_name,opc.instance_name,opc.cntr_valueINTO #Delta FROM sys.dm_os_performance_counters opcWHERE opc.counter_name LIKE '%/sec%' OR opc.counter_name LIKE '%KB/s%';--等待特定时间，然后继续执行后续的语句WAITFOR DELAY '00:00:10'INSERT INTO dbo.IndexingCounters ( create_date , server_name , object_name , counter_name , instance_name , Calculated_Counter_value )SELECT GETDATE(),LEFT(opc.object_name,CHARINDEX(':',opc.object_name)-1),SUBSTRING(opc.object_name,1+ CHARINDEX(':',opc.object_name),LEN(opc.object_name)),opc.counter_name,opc.instance_name,CASE WHEN opc.cntr_type=537003264THEN CONVERT (FLOAT,opc.cntr_value)/COALESCE(x.cntr_RatioBaseValue,-1)WHEN b.cntr_value IS NOT NULL THEN (opc.cntr_value -b.cntr_value)/5.ELSE opc.cntr_value END AS Calculated_Counter_valueFROM sys.dm_os_performance_counters opc LEFT OUTER JOIN (SELECT CASE cntr_value WHEN 0 THEN 1 ELSE cntr_value END AS cntr_RatioBaseValue,SUBSTRING(counter_name,1,PATINDEX('% Base%',counter_name)) AS counter_name_fixed,counter_name,[object_name],instance_name,cntr_typeFROM sys.dm_os_performance_counters WHERE cntr_type=1073939712)AS x ON opc.counter_name =x.counter_name_fixed AND opc.[object_name]=x.[object_name]AND opc.instance_name=x.instance_nameLEFT OUTER JOIN #Delta b ON opc.counter_name=b.counter_name AND opc.object_name=b.counter_nameAND opc.instance_name=b.instance_nameWHERE opc.cntr_type <> 1073939712AND ((opc.[object_name] LIKE '%Access Methods%' AND ( opc.counter_name LIKE 'Forwarded Records/sec'OR opc.counter_name LIKE '%FreeSpace Scans/sec%'OR opc.counter_name LIKE '%Full Scans/sec%'OR opc.counter_name LIKE '%Index Searches/sec%'OR opc.counter_name LIKE '%Page Splits/sec%'))OR (opc.[object_name] LIKE 'Buffer Manager'AND (opc.counter_name LIKE '%Page life expectancy%'OR opc.counter_name LIKE '%Page lookups/sec%'))OR (opc.[object_name] LIKE '%Locks%'AND (opc.counter_name LIKE '%Lock Wait Time(ms)%'OR opc.counter_name LIKE '%Lock Waits/sec%'OR opc.counter_name LIKE '%Number of Deadlocks/sec%'))OR (opc.[object_name] LIKE '%SQL Statistics%' AND opc.counter_name LIKE '%Batch Requests/sec%' ))

这是一次初始化的过程，随着对数据库的增加，可以从中获得更多的信息，并且可用其与初始值进行对比，创建一个表用于存放基准值，以便后续作为对比之用

CREATE TABLE dbo.IndexingCountersBaseline ( countter_baseline_id INT IDENTITY(1,1), start_date DATETIME, end_date DATETIME, server_name VARCHAR(128) NOT NULL, object_name VARCHAR(128) NOT NULL, counter_name VARCHAR(128) NOT NULL, instacn_name VARCHAR(128) NULL, minimum_counter_value FLOAT NULL, maximum_counter_value FLOAT NULL, average_counter_value FLOAT NULL, standard_deviation_counter_value FLOAT NULL, CONSTRAINT PK_IndexingCounterBaseline PRIMARY KEY CLUSTERED(countter_baseline_id) )

在收集数据时，最好选择系统比较稳定的时期收集，并存在IndexingCounters表，然后汇总特定时间内的数据存入IndexingCounterBaseline。最后定期汇总数据，并检查数据的波动情况

DECLARE @StartDate DATETIME ='20120301', @EndDate DATETIME ='20120325' INSERT INTO dbo.IndexingCountersBaseline ( start_date , end_date , server_name , object_name , counter_name , instacn_name , minimum_counter_value , maximum_counter_value , average_counter_value , standard_deviation_counter_value ) SELECT MIN(create_date), MAX(create_date), server_name, object_name, counter_name, instance_name, MIN(calculated_Counter_value), MAX(calculated_Counter_value), AVG(calculated_Counter_value),--返回给定表达式中所有值的统计标准偏差。 STDEV(calculated_Counter_value) FROM dbo.IndexingCounters WHERE create_date BETWEEN @StartDate AND @EndDate GROUP BY server_name, object_name, counter_name, instance_name

2.动态管理对象（DMO）

（1）.索引使用统计信息

1） 创建一个快照表，存入当前DMO中的数据，作为初始化用

2）周期性的统计和对比快照表中最近的数据和快照中前一个时期的数据，然后把差异的数据插入到历史表中

CREATE TABLE dbo.index_usage_stats_snapshot ( snapshot_id int IDENTITY(1,1) create_date datetime, database_id INT NOT NULL, object_id INT NOT NULL, index_id INT NOT NULL, user_seeks BIGINT NOT NULL, user_scans BIGINT NOT NULL, user_lookups BIGINT NOT NULL, user_updates BIGINT NOT NULL, last_user_seek DATETIME, last_user_scan DATETIME, last_user_lookup DATETIME, lasr_user_update DATETIME, system_seeks BIGINT NOT NULL, system_scans BIGINT NOT NULL, system_lookups BIGINT NOT NULL, system_updates BIGINT NOT NULL, last_system_seek DATETIME, last_system_scan DATETIME, last_system_lookup DATETIME, last_system_update DATETIME, CONSTRAINT PK_IndexUsageStatsSnapshot PRIMARY KEY CLUSTERED (snapshot_id), CONSTRAINT UQ_IndexUsageStatsSnapshot UNIQUE(create_date,database_id,object_id,index_id) ); CREATE TABLE dbo.index_usage_stats_history (history_id int IDENTITY(1,1) create_date datetime, database_id INT NOT NULL, object_id INT NOT NULL, index_id INT NOT NULL, user_seeks BIGINT NOT NULL, user_scans BIGINT NOT NULL, user_lookups BIGINT NOT NULL, user_updates BIGINT NOT NULL, last_user_seek DATETIME, last_user_scan DATETIME, last_user_lookup DATETIME, last_user_update DATETIME, system_seeks BIGINT NOT NULL, system_scans BIGINT NOT NULL, system_lookups BIGINT NOT NULL, system_updates BIGINT NOT NULL, last_system_seek DATETIME, last_system_scan DATETIME, last_system_lookup DATETIME, last_system_update DATETIME, CONSTRAINT PK_IndexUsageStatsHistory PRIMARY KEY CLUSTERED(history_id) CONSTRAINT UQ_IndexUsageStatsHIstory UNIQUE (create_date,database_id,object_id,index_id) );

历史表的数据来源于sys.dm_db_index_usage_stats(比如没4小时运行一次)，然后存入历史表

--创建完毕后初始化一次快照表

INSERT INTO dbo.index_usage_stats_snapshot SELECT GETDATE(), database_id , object_id , 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

--比对结果存放在历史表中

WITH IndexUsageCTE AS ( SELECT DENSE_RANK() OVER (ORDER BY create_date DESC) AS HistoryID, create_date, database_id,object_id, 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 dbo.index_usage_stats_snapshot ) --两次数据进行对比，然后插入历史表 INSERT INTO dbo.index_usage_stats_history SELECT il.create_date , il.database_id , il. object_id , il.index_id , il. user_seeks - COALESCE(i2.user_seeks,0), il. user_scans -COALESCE(i2.user_scans,0), il. user_lookups -COALESCE(i2.user_lookups,0), il. user_updates -COALESCE(i2.user_updates,0), il. last_user_seek , il. last_user_scan , il. last_user_lookup , il.last_user_update , il.system_seeks -COALESCE(i2.system_seeks,0), il. system_scans -COALESCE(i2.system_scans,0), il. system_lookups -COALESCE(i2.system_lookups,0), il. system_updates -COALESCE(i2.system_updates,0), il. last_system_seek , il.last_system_scan , il. last_system_lookup , il. last_system_update FROM IndexUsageCTE il LEFT OUTER JOIN IndexUsageCTE i2 ON il.database_id=i2.database_id AND il.object_id=i2.object_id AND il.index_id=i2.index_id AND i2.HistoryID=2 --检查没有小于0的数据 AND NOT (il.system_seeks-COALESCE(i2.system_seeks,0)<0 AND il.system_scans- COALESCE(i2.system_scans,0)<0 AND il.system_lookups-COALESCE(i2.system_lookups,0)<0 AND il.system_updates-COALESCE(i2.user_updates,0)<0 AND il.user_seeks-COALESCE(i2.user_seeks,0)<0 AND il.user_scans-COALESCE(i2.user_scans,0)<0 AND il.user_lookups-COALESCE(i2.user_lookups,0)<0 AND il.system_updates-COALESCE(i2.user_updates,0)<0 ) WHERE il.HistoryID=1 AND ( il.system_seeks-COALESCE(i2.system_seeks,0)>0 OR il.system_scans-COALESCE(i2.system_scans,0)>0 OR il.system_lookups-COALESCE(i2.system_lookups,0)>0 OR il.system_updates-COALESCE(i2.system_updates,0)>0 OR il.user_seeks-COALESCE(i2.user_seeks,0)>0 OR il.user_scans-COALESCE(i2.user_scans,0)>0 OR il.user_lookups-COALESCE(i2.user_lookups,0)>0 OR il.user_updates-COALESCE(i2.user_updates,0)>0 ) --对比每个周期索引的使用情况 SELECT * FROM dbo.index_usage_stats_history ORDER BY object_id

--对比每个周期索引的使用情况

SELECT * FROM dbo.index_usage_stats_history ORDER BY object_id

通过上面的对比，可以看到每个索引在特定周期的使用情况，其中的具体数值并没有大意义，重点关注那些短期内次数增长非常迅速的部分

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。