索引分析
1.索引方法论
索引方法论包含3个步骤：监控分析和实施。这三步是循环运行的，先监控后分析最后实施，然后在监控在分析在实施，知道没有明显的和潜在的索引问题为止。
2.监控
常见的监控工具有下面的3种：
1.性能监视器
2.动态管理对象
3.SQL Trace
IF DB_ID(N'IndexDemo') IS NOT NULL
DROP DATABASE IndexDemo
CREATE DATABASE IndexDemo
go
--创建一个表，用于存放对应计数器的值：
USE IndexDemo
GO
IF OBJECT_ID(N'IndexingCounters', 'U') IS NOT NULL
DROP TABLE IndexingCounters
CREATE 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 )
)
GO
CREATE NONCLUSTERED INDEX IX_IndexingCounters_CounterName
ON 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 IndexDemo
CREATE DATABASE IndexDemo
go
--创建一个表，用于存放对应计数器的值：
USE IndexDemo
GO
IF OBJECT_ID(N'IndexingCounters', 'U') IS NOT NULL
DROP TABLE IndexingCounters
CREATE 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 )
)
GO
CREATE NONCLUSTERED INDEX IX_IndexingCounters_CounterName
ON dbo.IndexingCounters (counter_name)
INCLUDE (create_date,server_name,object_name,Calculated_Counter_value)
USE IndexDemo
IF OBJECT_ID('tempdb..#Delta') IS NOT NULL
DROP TABLE #Delta
SELECT
opc.object_name,
opc.counter_name,
opc.instance_name,
opc.cntr_value
INTO #Delta
FROM sys.dm_os_performance_counters opc
WHERE 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=537003264
THEN 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_value
FROM 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_type
FROM 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_name
LEFT OUTER JOIN #Delta b ON opc.counter_name=b.counter_name
AND opc.object_name=b.counter_name
AND opc.instance_name=b.instance_name
WHERE opc.cntr_type <> 1073939712
AND ((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
通过上面的对比，可以看到每个索引在特定周期的使用情况，其中的具体数值并没有大意义，重点关注那些短期内次数增长非常迅速的部分
