索引优化--汇总

思路 ：先收集索引的信息，查看已有索引使用情况，删除不使用的索引，合并可以合并的索引，然后再重新收集信息，查看缺失的索引，最后建立索引。

第一步：收集索引信息

 

 

SELECT o.name AS tableName,
i.index_id ,
i.name AS indexName,
i.type AS indexType,
i.type_desc AS indexType_Desc ,
SUBSTRING(ikey.cols, 3, LEN(ikey.cols)) AS key_cols ,
SUBSTRING(inc.cols, 3, LEN(inc.cols)) AS included_cols ,
STATS_DATE(o.object_id, i.index_id) AS stats_date ,
i.filter_definition
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
CROSS APPLY ( SELECT ', ' + c.name + CASE ic.is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR
XML PATH('')
) AS ikey ( cols )
OUTER APPLY ( SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR
XML PATH('')
) AS inc ( cols )
WHERE o.type='U' AND i.type IN (1,2)
ORDER BY o.name ,
i.index_id

第二步：收集索引的使用情况

 

CREATE TABLE [dbo].[index_usage](
[DBname] [nvarchar](50) NULL,
[TableName] [nvarchar](50) NULL,
[IndexName] [nvarchar](100) NULL,
[index_id] [int] NULL,
[TotalWrites] [int] NULL,
[TotalReads] [int] NULL,
[Difference] [int] NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL,
[RecordTime] [datetime] NULL
) ON [PRIMARY]

 

-------索引被使用的情况
INSERT dbo.index_usage
( DBname ,
TableName ,
IndexName ,
index_id ,
TotalWrites ,
TotalReads ,
Difference ,
last_user_seek ,
last_user_scan ,
last_user_lookup ,
last_user_update ,
RecordTime
)
SELECT DB_NAME(ddius.database_id) DBname ,
OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] ,
ddius.last_user_seek ,
ddius.last_user_scan ,
ddius.last_user_lookup ,
ddius.last_user_update ,
GETDATE()
FROM sys.indexes AS i WITH ( NOLOCK )
LEFT JOIN sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
--AND ddius.database_id = DB_ID('MultiCompany')
--AND OBJECT_NAME(ddius.[object_id]) = 'AccountListOfPaltform'
AND i.index_id > 1 --非聚集索引
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;

第三步：索引使用详情

CREATE TABLE [dbo].[Index_query_plan](
[database_name] [nvarchar](max) NULL,
[TableName] NVARCHAR(max) NULL,
[OBJECT_NAME] [nvarchar](max) NULL,
[IndexName] [nvarchar](max) NULL,
[objtype] [nvarchar](50) NULL,
[query_plan] [xml] NULL,
[use_counts] [int] NULL,
[sql_text] [xml] NULL,
[RecordTime] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

INSERT dbo.Index_query_plan
( database_name ,
OBJECT_NAME ,
objtype ,
query_plan ,
use_counts ,
sql_text ,
RecordTime
)
SELECT COALESCE(DB_NAME(p.dbid)
, p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))
AS database_name
,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid)
+ '.' + OBJECT_NAME(p.objectid, p.dbid) AS object_name
,cp.objtype
,p.query_plan
,cp.UseCounts AS use_counts
,CAST('<?query --' + CHAR(13) + q.text + CHAR(13) + '--?>' AS XML) AS sql_text
,GETDATE()
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE cp.cacheobjtype = 'Compiled Plan'
AND p.query_plan.exist('//RelOp[@Parallel = "1"]') = 1
ORDER BY COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@
Database)[1]','nvarchar(128)')), UseCounts DESC

----获取使用到有IndexName
UPDATE A
SET A.IndexName = CAST(A.query_plan.query('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
data(//IndexScan/Object/@Index)') AS VARCHAR(MAX)),
A.tablename=CAST(A.query_plan.query('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
data(//IndexScan/Object/@Table)')AS VARCHAR(MAX))
--SELECT CAST(A.query_plan.query('
-- declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
-- data(//IndexScan/Object/@Index)
--') AS VARCHAR(MAX)) IndexName,
--CAST(A.query_plan.query('
-- declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
-- data(//IndexScan/Object/@Table)
--') AS VARCHAR(MAX)) TableName,*
FROM Index_query_plan A
WHERE A.IndexName IS NULL

 

 

然后要优化某一条索引时，可以在存储的表中查询：

select * from Index_query_plan A
where A.query_plan.exist('//Object[@Index = "[索引名]"]') = 1

 

注意：

这个语句查询的是XML格式的执行计划，XML是大小写区分的，所以要严格按照索引名（在SSMS中查到的名字）来替换，否则查询不出来

 

第四步：缺失的索引

 

CREATE TABLE [dbo].[Index_optimize](
[表] [nvarchar](255) NULL,
[相等列] [nvarchar](255) NULL,
[不相等列] [nvarchar](255) NULL,
[包含列] [nvarchar](max) NULL,
[总查询次数] [int] NULL,
[平均百分比收益] [decimal](18, 2) NULL,
[平均成本] [decimal](18, 2) NULL,
[可能改进] [decimal](18, 2) NULL,
[CreateSql] [nvarchar](max) NULL,
[CreateTime] [datetime] NULL
) ON [PRIMARY]

SELECT *,GETDATE()
FROM ( SELECT statement AS 表 ,
equality_columns AS 相等列 ,
inequality_columns AS 不相等列 ,
included_columns AS 包含列 ,
user_scans + user_seeks AS 总查询次数 ,
avg_user_impact AS 平均百分比收益 ,
avg_total_user_cost AS 平均成本 ,
avg_total_user_cost * avg_user_impact * ( user_scans
+ user_seeks ) AS 可能改进 ,
'CREATE INDEX [IX_' + obj.name + '_'
+ CONVERT(VARCHAR(32), GS.group_handle) + '_'
+ CONVERT(VARCHAR(32), D.index_handle) + ']' + ' ON '
+ [statement] + ' (' + ISNULL(equality_columns, '')
+ CASE WHEN equality_columns IS NOT NULL
AND inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(inequality_columns, '') + ')'
+ ISNULL(' INCLUDE (' + included_columns + ')', '') AS Create_Index_Syntax
FROM sys.dm_db_missing_index_details AS D
INNER JOIN sys.dm_db_missing_index_groups G ON G.index_handle = D.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
INNER JOIN sys.objects AS obj ON obj.object_id = OBJECT_ID([statement])
AND obj.type = 'U'
) X
--WHERE 表 = '[MultiCompany].[Finance].[AccountListOfPaltform]'
-- --AND (相等列 LIKE '%ReceiveMethod %' OR 相等列 LIKE '%ReceiptsStatus%')
-- --AND 包含列 LIKE '%cost%'
ORDER BY 可能改进 DESC

 

 

原文：https://www.cnblogs.com/binghou/p/9109635.html