Downloaded from http://www.jasonstrate.com/2013/05/index-analysis-may-update/
Help Link: http://www.jasonstrate.com/2010/11/return-of-the-index-analysis-report/
USE master
GO
IF OBJECT_ID('dbo.sp_IndexAnalysis') IS NOT NULL
DROP PROCEDURE dbo.sp_IndexAnalysis
GO
/*********************************************************************************************
Index Analysis Script v11.06
(C) 2013, Jason Strate
Feedback:
mailto:jasonstrate@gmail.com
http://www.jasonstrate.com
License:
This query is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this query, in whole or in part, is prohibited without the author's express
written consent.
Note:
Recommendations in the Index Action column are not black and white recommendations.
They are more light grey ideas of what may be appropriate. Always use your experience
with the database in place of a blanket recommendation.
The information in the DMVs is gathered from when the SQL Server service last started, the
database was brought onling, or when the index metadata was updated (such as during reindex
operations, which ever event is more recent.
The index statistics accumulated in sys.dm_db_index_operational_stats and
sys.dm_db_index_usage_stats are reset when the index is rebuilt.
The index statistics for a table that are accumulated in the DMVs
sys.dm_db_missing_index_* are reset whenever an index is created on the table.
The index name provided in the name column for indexes that do not exist is not a
recommended name for the index. It’s just an informative placeholder.
Parameters
@TableName(NVARCHAR(256)): Optional parameter to add
@IncludeMissingIndexes(BIT): Identifies whether to include missing indexes in the output
@IncludeMissingFKIndexes(BIT): Identifies whether to include missing foreign key indexes
in the output
@Output(VARCHAR(20)): Determines the output results from the stored procedure. The
available values are
* DETAILED: All results from the index analysis
* DUPLICATE: Index results for deplicate indexes
* OVERLAPPING: Index results for overlapping indexes
Columns:
index_action: Analysis recommendation on action to take on the index
CREATE: Recommend adding the index to the table.
DROP-DUP: Recommend dropping the index since it is a duplicate
DROP-COUNT: Count of indexes on tables may indicate that the index may be a candidate
for removal.
DROP-USAGE: Usage of the index suggests it may be a candidate for removal
BLEND: Review the missing index details to see if the missing index details can be
added to an existing index.
REALIGN: Bookmark lookups on the index exceed the number of seeks on the table.
Recommend investigating whether to move the clustered index to another index or
add included columns to the indexes that are part of the bookmark lookups.
index_pros: list of reasons that indicate the benefits provided by the index
FK: The index schema maps to a foreign key
UQ: Index is a unique constraint
$, $$, $$$, $$$+: Indicates the ratio of read to write uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to greater benefit
provided by the index.
index_cons: list of reasons that indicate some negative aspects associate with the index
SCN: Flag indicating that the ratio of seeks to scans on the index less than 1,000.
DP: Index schema is a duplicate of another index.
OV: Index schema overlaps another index.
$, $$, $$$, $$$+: Indicates the ratio of write to read uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to more cost
incurred by the index.
DSB: Index is disabled. These should be enabled or removed.
filegroup: file group that the index is located.
schema_id: Schema ID
schema_name: Name of the schema.
object_id: Object ID
table_name: Name of the table name
index_id: Index ID
index_name: Name of the index.
is_unique: Flag indicating whether an index has a unique index.
has_unique: Flag indicating whether the table has a unique index.
type_desc: Type of index; either clustered or non-clustered.
partition_number: Partition number.
fill_factor: Percentage of free space left on pages the index was created or rebuilt.
is_padded: Boolean value indicating whether fill factor is applied to nonleaf levels
reserved_page_count: Total number of pages reserved for the index.
size_in_mb: The amount of space in MB the index utilizes on disk.
buffered_page_count: Total number of pages in the buffer for the index.
buffer_mb: The amount of space in MB in the buffer for the index.
pct_in_buffer: The percentage of an index that is current in the SQL Server buffer.
table_buffer_mb: The amount of space in MB in the SQL Server buffer that is being
utilized by the table.
row_count: Number of rows in the index.
missing_index_impact: Calculation of impact of a potential index. This is based on the seeks and
scans that the index could have utilized multiplied by average improvement the index
would have provided. This is included only for missing indexes.
existing_ranking: Ranking of the existing indexes ordered by user_total descending across
the indexes for the table.
user_total: Total number of seek, scan, and lookup operations for the index.
user_total_pct: Percentage of total number of seek, scan, and lookup operations for this
index compared to all seek, scan, and lookup operations for existing indexes for the
table.
estimated_user_total_pct: Percentage of total number of seek, scan, and lookup operations
for this index compared to all seek, scan, and lookup operations for existing and
potential indexes for the table. This number is naturally skewed because a seek for
potential Index A resulted in another operation on an existing index and both of
these operations would be counted.
user_seeks: Number of seek operations on the index.
user_scans: Number of scan operations on the index.
user_lookups: Number of lookup operations on the index.
user_updates: Number of update operations on the index.
read_to_update_ratio: Ratio of user_seeks, user_scans, and user_lookups to user_updates.
read_to_update: Division of user_seeks, user_scans, and user_lookups by user_updates.
update_to_read: Division of user_updates to user_seeks, user_scans by user_lookups.
row_lock_count: Cumulative number of row locks requested.
row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.
row_lock_wait_in_ms: Total number of milliseconds the Database Engine waited on a row
lock.
row_block_pct: Percentage of row locks that encounter waits on a row lock.
avg_row_lock_waits_ms: Average number of milliseconds the Database Engine waited on a row
lock.
page_latch_wait_count: Cumulative number of times the page latch waits occurred
avg_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page latch wait.
page_io_latch_wait_count: Cumulative number of times the page IO latch waits occurred
avg_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page IO latch wait.
tree_page_latch_wait_count: Cumulative number of times the tree page latch waits occurred
avg_tree_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on
a tree page latch wait.
tree_page_io_latch_wait_count: Cumulative number of times the tree page IO latch waits
occurred
avg_tree_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited
on a tree page IO latch wait.
read_operations: Cumulative count of range_scan_count and singleton_lookup_count
operations
leaf_writes: Cumulative count of leaf_insert_count, leaf_update_count, leaf_delete_count
and leaf_ghost_count operations
leaf_page_allocations: Cumulative count of leaf-level page allocations in the index or
heap. For an index, a page allocation corresponds to a page split.
leaf_page_merges: Cumulative count of page merges at the leaf level.
nonleaf_writes: Cumulative count of leaf_insert_count, leaf_update_count and
leaf_delete_count operations
nonleaf_page_allocations: Cumulative count of page allocations caused by page splits
above the leaf level.
nonleaf_page_merges: Cumulative count of page merges above the leaf level.
indexed_columns: Columns that are part of the index, missing index or foreign key.
included_columns: Columns that are included in the index or missing index.
indexed_columns_ids: Column IDs that are part of the index, missing index or foreign
key
included_column_ids: Column IDs that are included in the index or missing index.
duplicate_indexes: List of Indexes that exist on the table that are identical to the
index on this row.
overlapping_indexes: List of Indexes that exist on the table that overlap the index on
this row.
related_foreign_keys: List of foreign keys that are related to the index either as an
exact match or covering index.
related_foreign_keys_xml: XML document listing foreign keys that are related to the index
either as an exact match or covering index.
Revision History
Date Version DESCRIPTION
----------------------------------------------------------------------------------------------
2012-12-10 11.01 Rewrote information collection to reduce dynamic SQL
* Fixed issue with Object_ID
* Added unique check and filter definition to duplication and
overlapping indexes
* Fixed errors with documentation section.
* Added parameters @IncludeMissingIndexes, @IncludeMissingFKIndexes,
and @Output
* Removed DDL create and drop columns for indexes, too much risk in
automatic index building
2012-12-28 11.02 * Fixed new issues WITH Object_ID
* change missing foreign key logic to include basic details but not
suggest missing indexes with @IncludeMissingFKIndexes flag
* Fixed issues with missing foreign keys that was not displaying them
* Improved default sort for DETAILED output
* Updated case for case-sensitive collations
2013-01-08 11.03 * Fixed issue with overlapping indexes and duplicate indexes related
filtered indexes
* Improved error handling to solve issue with truncation MESSAGE
2013-03-07 11.04 * Fixed duplicate index logic to match that from
http://www.jasonstrate.com/2013/03/thats-actually-a-duplicate-index/
* Added check for disabled indexes to checks
* Expanded drop reasons with explanations and descriptions for
dropping because of duplication, usage, and count of indexes
* Increased size of foreign key name to accommodate larger names
* Fixed issue causing DROP recommmendation on missing indexes
2013-03-20 11.05 * Fixed data type on missing_index_impact (Credit: Dick Baker)
2013-05-22 11.06 * Fixed bug causing mismatch with foreign key analysis
* Adjusted overlapping index logic which was broke with new duplicate
index logic
*********************************************************************************************/
CREATE PROCEDURE dbo.sp_IndexAnalysis
(
@TableName NVARCHAR(256) = NULL
,@IncludeMissingIndexes BIT = 1
,@IncludeMissingFKIndexes BIT = 1
,@Output VARCHAR(20) = 'DETAILED'
)
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @ERROR_MESSAGE NVARCHAR(2048)
,@ERROR_SEVERITY INT
,@ERROR_STATE INT
DECLARE @SQL NVARCHAR(max)
,@DB_ID INT
,@ObjectID INT
,@DatabaseName NVARCHAR(max)
BEGIN TRY
IF @Output NOT IN ('DETAILED','DUPLICATE','OVERLAPPING')
RAISERROR('The value "%s" provided for the @Output parameter is not valid',16,1,@Output)
SELECT @DB_ID = DB_ID()
,@ObjectID = OBJECT_ID(QUOTENAME(DB_NAME(DB_ID()))+'.'+COALESCE(QUOTENAME(PARSENAME(@TableName,2)),'')+'.'+QUOTENAME(PARSENAME(@TableName,1)))
,@DatabaseName = QUOTENAME(DB_NAME(DB_ID()))
-- Obtain memory buffer information on database objects
IF OBJECT_ID('tempdb..#MemoryBuffer') IS NOT NULL
DROP TABLE #MemoryBuffer
CREATE TABLE #MemoryBuffer (
database_id INT
,object_id INT
,index_id INT
,partition_number INT
,buffered_page_count INT
,buffered_mb DECIMAL(12, 2)
)
SET @SQL = 'WITH AllocationUnits
AS (
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
)
SELECT DB_ID()
,au.object_id
,au.index_id
,au.partition_number
,COUNT(*)AS buffered_page_count
,CONVERT(DECIMAL(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb
FROM '+@DatabaseName+'.sys.dm_os_buffer_descriptors AS bd
INNER JOIN AllocationUnits au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = db_id()
GROUP BY au.object_id, au.index_id, au.partition_number'
BEGIN TRY
INSERT INTO #MemoryBuffer
EXEC sys.sp_executesql @SQL
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #MemoryBuffer (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Obtain index meta data information
IF OBJECT_ID('tempdb..#IndexMeta') IS NOT NULL
DROP TABLE #IndexMeta
CREATE TABLE #IndexMeta (
database_id SMALLINT
,filegroup_name NVARCHAR(128)
,schema_id INT
,schema_name NVARCHAR(128)
,object_id INT
,table_name NVARCHAR(128)
,index_id INT
,index_name NVARCHAR(128)
,is_primary_key BIT
,is_unique BIT
,is_disabled BIT
,type_desc NVARCHAR(128)
,partition_number INT
,fill_factor TINYINT
,is_padded BIT
,reserved_page_count BIGINT
,size_in_mb DECIMAL(12, 2)
,row_count BIGINT
,filter_definition NVARCHAR(MAX)
,indexed_columns NVARCHAR(MAX)
,included_columns NVARCHAR(MAX)
,key_columns NVARCHAR(MAX)
,data_columns NVARCHAR(MAX)
,indexed_columns_ids NVARCHAR(1024)
,included_column_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT
database_id = DB_ID()
, filegroup = ds.name
, s.schema_id
, schema_name = s.name
, object_id = t.object_id
, table_name = t.name
, i.index_id
, index_name = COALESCE(i.name, ''N/A'')
, ps.partition_number
, i.is_primary_key
, i.is_unique
, i.is_disabled
, type_desc = CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc
, i.fill_factor
, i.is_padded
, ps.reserved_page_count
, size_in_mb = CAST(reserved_page_count * CAST(8 as float) / 1024 as DECIMAL(12,2))
, row_count
, i.filter_definition
, indexed_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, included_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, key_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ COALESCE((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),''''), 1, 2, '''')
, data_columns = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''') END
, indexed_column_ids = (SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ ''|'' + COALESCE((SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),'''')
+ CASE WHEN i.is_unique = 1 THEN ''U'' ELSE '''' END
, included_column_ids = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE
COALESCE((SELECT QUOTENAME(ic.column_id,''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), SPACE(0)) END
FROM '+@DatabaseName+'.sys.tables t
INNER JOIN '+@DatabaseName+'.sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN '+@DatabaseName+'.sys.indexes i ON t.object_id = i.object_id
INNER JOIN '+@DatabaseName+'.sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN '+@DatabaseName+'.sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id'
IF @ObjectID IS NOT NULL
SET @SQL = @SQL + CHAR(13) + 'WHERE t.object_id = @ObjectID '
BEGIN TRY
INSERT INTO #IndexMeta (
database_id
,filegroup_name
,schema_id
,schema_name
,object_id
,table_name
,index_id
,index_name
,partition_number
,is_primary_key
,is_unique
,is_disabled
,type_desc
,fill_factor
,is_padded
,reserved_page_count
,size_in_mb
,row_count
,filter_definition
,indexed_columns
,included_columns
,key_columns
,data_columns
,indexed_columns_ids
,included_column_ids)
EXEC sys.sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #IndexMeta (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
IF OBJECT_ID('tempdb..#IndexStatistics') IS NOT NULL
DROP TABLE #IndexStatistics
SELECT IDENTITY(INT,1,1) AS row_id
,CAST('' AS VARCHAR(10)) AS index_action
,CAST('' AS VARCHAR(25)) AS index_pros
,CAST('' AS VARCHAR(25)) AS index_cons
,im.database_id
,im.filegroup_name
,im.schema_id
,im.schema_name
,im.object_id
,im.table_name
,im.index_id
,im.index_name
,im.is_primary_key
,im.is_unique
,im.is_disabled
,CAST(0 AS BIT) AS has_unique
,im.type_desc
,im.partition_number
,im.fill_factor
,im.is_padded
,im.reserved_page_count
,im.size_in_mb
,mb.buffered_page_count
,mb.buffered_mb
,CAST(0 AS INT) AS table_buffered_mb
,CAST(100.*mb.buffered_page_count/NULLIF(im.reserved_page_count,0) AS DECIMAL(12,2)) AS buffered_percent
,im.row_count
,ROW_NUMBER() OVER (PARTITION BY im.object_id ORDER BY im.is_primary_key desc,ius.user_seeks + ius.user_scans + ius.user_lookups DESC) AS index_rank
, ius.user_seeks + ius.user_scans + ius.user_lookups AS user_total
, COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY im.object_id), 0) * 1.) as DECIMAL(6,2)),0) AS user_total_pct
,CAST(0 AS DECIMAL(6,2)) AS estimated_user_total_pct
,CAST(0 AS FLOAT) AS missing_index_impact -- Dick Baker 201303 (INT range not big enough and is f.p. anyway)
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.user_updates
,(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups))/NULLIF(ius.user_updates,0) AS read_to_update_ratio
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups >= ius.user_updates
THEN CEILING(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups)/COALESCE(NULLIF(ius.user_seeks,0),1))
ELSE 0 END AS read_to_update
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups <= ius.user_updates
THEN CEILING(1.*(ius.user_updates)/COALESCE(NULLIF(ius.user_seeks + ius.user_scans + ius.user_lookups,0),1))
ELSE 0 END AS update_to_read
,ios.row_lock_count
,ios.row_lock_wait_count
,ios.row_lock_wait_in_ms
,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count,0) AS DECIMAL(12,2)) AS row_block_pct
,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count,0) AS DECIMAL(12,2)) AS avg_row_lock_waits_ms
,ios.page_latch_wait_count
,CAST(1. * page_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_latch_wait_ms
,ios.page_io_latch_wait_count
,CAST(1. * ios.page_io_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_io_latch_wait_ms
,ios.tree_page_latch_wait_count AS tree_page_latch_wait_count
,CAST(1. * tree_page_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_latch_wait_ms
,ios.tree_page_io_latch_wait_count
,CAST(1. * ios.tree_page_io_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_io_latch_wait_ms
,range_scan_count + singleton_lookup_count AS read_operations
,ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count + ios.leaf_ghost_count AS leaf_writes
,leaf_allocation_count As leaf_page_allocations
,ios.leaf_page_merge_count AS leaf_page_merges
,ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count AS nonleaf_writes
,ios.nonleaf_allocation_count AS nonleaf_page_allocations
,ios.nonleaf_page_merge_count AS nonleaf_page_merges
,im.indexed_columns
,im.included_columns
,im.filter_definition
,key_columns
,data_columns
,im.indexed_columns_ids
,im.included_column_ids
,CAST('' AS VARCHAR(MAX)) AS duplicate_indexes
,CAST('' AS SMALLINT) AS first_dup_index_id
,CAST('' AS VARCHAR(MAX)) AS overlapping_indexes
,CAST('' AS VARCHAR(MAX)) AS related_foreign_keys
,CAST('' AS XML) AS related_foreign_keys_xml
INTO #IndexStatistics
FROM #IndexMeta im
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON im.object_id = ius.object_id AND im.index_id = ius.index_id AND im.database_id = ius.database_id
LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON im.object_id = ios.object_id AND im.index_id = ios.index_id AND im.partition_number = ios.partition_number
LEFT OUTER JOIN #MemoryBuffer mb ON im.object_id = mb.object_id AND im.index_id = mb.index_id AND im.partition_number = mb.partition_number
IF @IncludeMissingIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, missing_index_impact, index_rank, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
SELECT
'' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(mid.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name
,mid.object_id
,OBJECT_NAME(mid.object_id) AS table_name
,'--MISSING INDEX--' AS index_name
,'NONCLUSTERED' AS type_desc
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 AS index_rank
,migs.user_seeks + migs.user_scans as user_total
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + CASE WHEN inequality_columns IS NOT NULL THEN ', ' ELSE SPACE(0) END, SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = @DB_ID
END
-- Collect foreign key information.
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
DROP TABLE #ForeignKeys
CREATE TABLE #ForeignKeys
(
foreign_key_name NVARCHAR(256)
,object_id INT
,fk_columns NVARCHAR(max)
,fk_columns_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT fk.name + ''|PARENT'' AS foreign_key_name
,fkc.parent_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.parent_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL)
UNION ALL
SELECT fk.name + ''|REFERENCED'' as foreign_key_name
,fkc.referenced_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.referenced_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL)'
BEGIN TRY
INSERT INTO #ForeignKeys
(foreign_key_name, object_id, fk_columns, fk_columns_ids)
EXEC sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #ForeignKeys (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Determine duplicate, overlapping, and foreign key index information
UPDATE i
SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids
FOR XML PATH('')), 1, 2, '')
,first_dup_index_id = (SELECT MIN(index_id)
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id > iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids)
,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND LEFT(i.indexed_columns_ids, CHARINDEX('|',iibl.indexed_columns_ids,1)-1)
LIKE LEFT(iibl.indexed_columns_ids, CHARINDEX('|',i.indexed_columns_ids,1)-1) + '%'
AND Replace(i.indexed_columns_ids,'|','') <> Replace(iibl.indexed_columns_ids,'|','')
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
FROM #ForeignKeys ifk
WHERE ifk.object_id = i.object_id
AND i.indexed_columns_ids LIKE ifk.fk_columns_ids + '%'
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys_xml = CAST((SELECT foreign_key_name
FROM #ForeignKeys fk
WHERE fk.object_id = i.object_id
AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
FOR XML AUTO) as xml)
FROM #IndexStatistics i
IF @IncludeMissingFKIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, index_rank, indexed_columns, related_foreign_keys)
SELECT '' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(fk.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
,fk.object_id
,OBJECT_NAME(fk.object_id) AS table_name
,'--MISSING FOREIGN KEY--' AS index_name
,'NONCLUSTERED' AS type_desc
,9999
,fk.fk_columns
,fk.foreign_key_name
FROM #ForeignKeys fk
LEFT OUTER JOIN #IndexStatistics i ON fk.object_id = i.object_id AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
WHERE i.index_name IS NULL
END
-- Determine whether tables have unique indexes
UPDATE i
SET has_unique = 1
FROM #IndexStatistics i
WHERE EXISTS (SELECT * FROM #IndexStatistics ii WHERE i.object_id = ii.object_id AND ii.is_unique = 1)
-- Calculate estimated user total for each index.
;WITH Aggregation
AS (
SELECT row_id
,CAST(100. * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as DECIMAL(12,2)) AS estimated_user_total_pct
,SUM(buffered_mb) OVER(PARTITION BY schema_name, table_name) as table_buffered_mb
FROM #IndexStatistics
)
UPDATE ibl
SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
,table_buffered_mb = a.table_buffered_mb
FROM #IndexStatistics ibl
INNER JOIN Aggregation a ON ibl.row_id = a.row_id
-- Update Index Action information
;WITH IndexAction
AS (
SELECT row_id
,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
WHEN is_disabled = 1 THEN 'ENABLE'
WHEN duplicate_indexes IS NOT NULL AND first_dup_index_id IS NOT NULL AND index_id IS NOT NULL THEN 'DROP-DUP'
WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
WHEN type_desc = 'XML' THEN '---'
WHEN is_unique = 1 THEN '---'
WHEN related_foreign_keys IS NOT NULL THEN '---'
WHEN type_desc = '--NONCLUSTERED--' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc) <= 10 AND estimated_user_total_pct > 1 THEN 'CREATE'
WHEN type_desc = '--NONCLUSTERED--' THEN 'BLEND'
WHEN ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc, index_rank) > 10 AND index_id IS NOT NULL THEN 'DROP-COUNT'
WHEN index_id NOT IN (0,1) AND duplicate_indexes IS NULL AND user_total = 0 AND index_id IS NOT NULL
THEN 'DROP-USAGE'
ELSE '---' END AS index_action
FROM #IndexStatistics
)
UPDATE ibl
SET index_action = ia.index_action
FROM #IndexStatistics ibl INNER JOIN IndexAction ia
ON ibl.row_id = ia.row_id
-- Update Pro/Con statuses
UPDATE #IndexStatistics
SET index_pros = COALESCE(STUFF(CASE WHEN related_foreign_keys IS NOT NULL THEN ', FK' ELSE '' END
+ CASE WHEN is_unique = 1 THEN ', UQ' ELSE '' END
+ COALESCE(', ' + CASE WHEN read_to_update BETWEEN 1 AND 9 THEN '$'
WHEN read_to_update BETWEEN 10 AND 99 THEN '$$'
WHEN read_to_update BETWEEN 100 AND 999 THEN '$$$'
WHEN read_to_update > 999 THEN '$$$+' END, '')
,1,2,''),'')
,index_cons = COALESCE(STUFF(CASE WHEN user_seeks / NULLIF(user_scans,0) < 1000 THEN ', SCN' ELSE '' END
+ CASE WHEN duplicate_indexes IS NOT NULL THEN ', DP' ELSE '' END
+ CASE WHEN overlapping_indexes IS NOT NULL THEN ', OV' ELSE '' END
+ COALESCE(', ' + CASE WHEN update_to_read BETWEEN 1 AND 9 THEN '$'
WHEN update_to_read BETWEEN 10 AND 99 THEN '$$'
WHEN update_to_read BETWEEN 100 AND 999 THEN '$$$'
WHEN update_to_read > 999 THEN '$$$+' END, '')
+ CASE WHEN is_disabled = 1 THEN ', DSB' ELSE '' END
,1,2,''),'')
IF @Output = 'DETAILED'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, filter_definition
, is_primary_key
, is_unique
, is_disabled
, has_unique
, partition_number
, fill_factor
, is_padded
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, estimated_user_total_pct
, missing_index_impact
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_to_update_ratio
, read_to_update
, update_to_read
, row_lock_count
, row_lock_wait_count
, row_lock_wait_in_ms
, row_block_pct
, avg_row_lock_waits_ms
, page_latch_wait_count
, avg_page_latch_wait_ms
, page_io_latch_wait_count
, avg_page_io_latch_wait_ms
, tree_page_latch_wait_count
, avg_tree_page_latch_wait_ms
, tree_page_io_latch_wait_count
, avg_tree_page_io_latch_wait_ms
, read_operations
, leaf_writes
, leaf_page_allocations
, leaf_page_merges
, nonleaf_writes
, nonleaf_page_allocations
, nonleaf_page_merges
, duplicate_indexes
, overlapping_indexes
, related_foreign_keys
, related_foreign_keys_xml
, key_columns
, data_columns
FROM #IndexStatistics
WHERE (estimated_user_total_pct > 0.01 AND index_id IS NULL)
OR related_foreign_keys IS NOT NULL
OR index_id IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, COALESCE(user_total,-1) DESC, COALESCE(user_updates,-1) DESC, COALESCE(index_id,999)
END
ELSE IF @Output = 'DUPLICATE'
BEGIN
SELECT
DENSE_RANK() OVER (ORDER BY key_columns, data_columns) AS duplicate_group
, index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, duplicate_indexes
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE duplicate_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, RANK() OVER (ORDER BY key_columns, data_columns)
END
ELSE IF @Output = 'OVERLAPPING'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, overlapping_indexes
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE overlapping_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, user_total DESC
END
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Procedure Error (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
GO
USE AdventureWorks2012
GO
EXEC dbo.sp_IndexAnalysis @Output = 'DETAILED'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
EXEC dbo.sp_IndexAnalysis @Output = 'OVERLAPPING'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
Help Link: http://www.jasonstrate.com/2010/11/return-of-the-index-analysis-report/
USE master
GO
IF OBJECT_ID('dbo.sp_IndexAnalysis') IS NOT NULL
DROP PROCEDURE dbo.sp_IndexAnalysis
GO
/*********************************************************************************************
Index Analysis Script v11.06
(C) 2013, Jason Strate
Feedback:
mailto:jasonstrate@gmail.com
http://www.jasonstrate.com
License:
This query is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this query, in whole or in part, is prohibited without the author's express
written consent.
Note:
Recommendations in the Index Action column are not black and white recommendations.
They are more light grey ideas of what may be appropriate. Always use your experience
with the database in place of a blanket recommendation.
The information in the DMVs is gathered from when the SQL Server service last started, the
database was brought onling, or when the index metadata was updated (such as during reindex
operations, which ever event is more recent.
The index statistics accumulated in sys.dm_db_index_operational_stats and
sys.dm_db_index_usage_stats are reset when the index is rebuilt.
The index statistics for a table that are accumulated in the DMVs
sys.dm_db_missing_index_* are reset whenever an index is created on the table.
The index name provided in the name column for indexes that do not exist is not a
recommended name for the index. It’s just an informative placeholder.
Parameters
@TableName(NVARCHAR(256)): Optional parameter to add
@IncludeMissingIndexes(BIT): Identifies whether to include missing indexes in the output
@IncludeMissingFKIndexes(BIT): Identifies whether to include missing foreign key indexes
in the output
@Output(VARCHAR(20)): Determines the output results from the stored procedure. The
available values are
* DETAILED: All results from the index analysis
* DUPLICATE: Index results for deplicate indexes
* OVERLAPPING: Index results for overlapping indexes
Columns:
index_action: Analysis recommendation on action to take on the index
CREATE: Recommend adding the index to the table.
DROP-DUP: Recommend dropping the index since it is a duplicate
DROP-COUNT: Count of indexes on tables may indicate that the index may be a candidate
for removal.
DROP-USAGE: Usage of the index suggests it may be a candidate for removal
BLEND: Review the missing index details to see if the missing index details can be
added to an existing index.
REALIGN: Bookmark lookups on the index exceed the number of seeks on the table.
Recommend investigating whether to move the clustered index to another index or
add included columns to the indexes that are part of the bookmark lookups.
index_pros: list of reasons that indicate the benefits provided by the index
FK: The index schema maps to a foreign key
UQ: Index is a unique constraint
$, $$, $$$, $$$+: Indicates the ratio of read to write uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to greater benefit
provided by the index.
index_cons: list of reasons that indicate some negative aspects associate with the index
SCN: Flag indicating that the ratio of seeks to scans on the index less than 1,000.
DP: Index schema is a duplicate of another index.
OV: Index schema overlaps another index.
$, $$, $$$, $$$+: Indicates the ratio of write to read uses in execution plans. The
higher the ratio the more dollar signs; this should correlate to more cost
incurred by the index.
DSB: Index is disabled. These should be enabled or removed.
filegroup: file group that the index is located.
schema_id: Schema ID
schema_name: Name of the schema.
object_id: Object ID
table_name: Name of the table name
index_id: Index ID
index_name: Name of the index.
is_unique: Flag indicating whether an index has a unique index.
has_unique: Flag indicating whether the table has a unique index.
type_desc: Type of index; either clustered or non-clustered.
partition_number: Partition number.
fill_factor: Percentage of free space left on pages the index was created or rebuilt.
is_padded: Boolean value indicating whether fill factor is applied to nonleaf levels
reserved_page_count: Total number of pages reserved for the index.
size_in_mb: The amount of space in MB the index utilizes on disk.
buffered_page_count: Total number of pages in the buffer for the index.
buffer_mb: The amount of space in MB in the buffer for the index.
pct_in_buffer: The percentage of an index that is current in the SQL Server buffer.
table_buffer_mb: The amount of space in MB in the SQL Server buffer that is being
utilized by the table.
row_count: Number of rows in the index.
missing_index_impact: Calculation of impact of a potential index. This is based on the seeks and
scans that the index could have utilized multiplied by average improvement the index
would have provided. This is included only for missing indexes.
existing_ranking: Ranking of the existing indexes ordered by user_total descending across
the indexes for the table.
user_total: Total number of seek, scan, and lookup operations for the index.
user_total_pct: Percentage of total number of seek, scan, and lookup operations for this
index compared to all seek, scan, and lookup operations for existing indexes for the
table.
estimated_user_total_pct: Percentage of total number of seek, scan, and lookup operations
for this index compared to all seek, scan, and lookup operations for existing and
potential indexes for the table. This number is naturally skewed because a seek for
potential Index A resulted in another operation on an existing index and both of
these operations would be counted.
user_seeks: Number of seek operations on the index.
user_scans: Number of scan operations on the index.
user_lookups: Number of lookup operations on the index.
user_updates: Number of update operations on the index.
read_to_update_ratio: Ratio of user_seeks, user_scans, and user_lookups to user_updates.
read_to_update: Division of user_seeks, user_scans, and user_lookups by user_updates.
update_to_read: Division of user_updates to user_seeks, user_scans by user_lookups.
row_lock_count: Cumulative number of row locks requested.
row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.
row_lock_wait_in_ms: Total number of milliseconds the Database Engine waited on a row
lock.
row_block_pct: Percentage of row locks that encounter waits on a row lock.
avg_row_lock_waits_ms: Average number of milliseconds the Database Engine waited on a row
lock.
page_latch_wait_count: Cumulative number of times the page latch waits occurred
avg_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page latch wait.
page_io_latch_wait_count: Cumulative number of times the page IO latch waits occurred
avg_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited on a
page IO latch wait.
tree_page_latch_wait_count: Cumulative number of times the tree page latch waits occurred
avg_tree_page_latch_wait_ms: Average number of milliseconds the Database Engine waited on
a tree page latch wait.
tree_page_io_latch_wait_count: Cumulative number of times the tree page IO latch waits
occurred
avg_tree_page_io_latch_wait_ms: Average number of milliseconds the Database Engine waited
on a tree page IO latch wait.
read_operations: Cumulative count of range_scan_count and singleton_lookup_count
operations
leaf_writes: Cumulative count of leaf_insert_count, leaf_update_count, leaf_delete_count
and leaf_ghost_count operations
leaf_page_allocations: Cumulative count of leaf-level page allocations in the index or
heap. For an index, a page allocation corresponds to a page split.
leaf_page_merges: Cumulative count of page merges at the leaf level.
nonleaf_writes: Cumulative count of leaf_insert_count, leaf_update_count and
leaf_delete_count operations
nonleaf_page_allocations: Cumulative count of page allocations caused by page splits
above the leaf level.
nonleaf_page_merges: Cumulative count of page merges above the leaf level.
indexed_columns: Columns that are part of the index, missing index or foreign key.
included_columns: Columns that are included in the index or missing index.
indexed_columns_ids: Column IDs that are part of the index, missing index or foreign
key
included_column_ids: Column IDs that are included in the index or missing index.
duplicate_indexes: List of Indexes that exist on the table that are identical to the
index on this row.
overlapping_indexes: List of Indexes that exist on the table that overlap the index on
this row.
related_foreign_keys: List of foreign keys that are related to the index either as an
exact match or covering index.
related_foreign_keys_xml: XML document listing foreign keys that are related to the index
either as an exact match or covering index.
Revision History
Date Version DESCRIPTION
----------------------------------------------------------------------------------------------
2012-12-10 11.01 Rewrote information collection to reduce dynamic SQL
* Fixed issue with Object_ID
* Added unique check and filter definition to duplication and
overlapping indexes
* Fixed errors with documentation section.
* Added parameters @IncludeMissingIndexes, @IncludeMissingFKIndexes,
and @Output
* Removed DDL create and drop columns for indexes, too much risk in
automatic index building
2012-12-28 11.02 * Fixed new issues WITH Object_ID
* change missing foreign key logic to include basic details but not
suggest missing indexes with @IncludeMissingFKIndexes flag
* Fixed issues with missing foreign keys that was not displaying them
* Improved default sort for DETAILED output
* Updated case for case-sensitive collations
2013-01-08 11.03 * Fixed issue with overlapping indexes and duplicate indexes related
filtered indexes
* Improved error handling to solve issue with truncation MESSAGE
2013-03-07 11.04 * Fixed duplicate index logic to match that from
http://www.jasonstrate.com/2013/03/thats-actually-a-duplicate-index/
* Added check for disabled indexes to checks
* Expanded drop reasons with explanations and descriptions for
dropping because of duplication, usage, and count of indexes
* Increased size of foreign key name to accommodate larger names
* Fixed issue causing DROP recommmendation on missing indexes
2013-03-20 11.05 * Fixed data type on missing_index_impact (Credit: Dick Baker)
2013-05-22 11.06 * Fixed bug causing mismatch with foreign key analysis
* Adjusted overlapping index logic which was broke with new duplicate
index logic
*********************************************************************************************/
CREATE PROCEDURE dbo.sp_IndexAnalysis
(
@TableName NVARCHAR(256) = NULL
,@IncludeMissingIndexes BIT = 1
,@IncludeMissingFKIndexes BIT = 1
,@Output VARCHAR(20) = 'DETAILED'
)
WITH RECOMPILE
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @ERROR_MESSAGE NVARCHAR(2048)
,@ERROR_SEVERITY INT
,@ERROR_STATE INT
DECLARE @SQL NVARCHAR(max)
,@DB_ID INT
,@ObjectID INT
,@DatabaseName NVARCHAR(max)
BEGIN TRY
IF @Output NOT IN ('DETAILED','DUPLICATE','OVERLAPPING')
RAISERROR('The value "%s" provided for the @Output parameter is not valid',16,1,@Output)
SELECT @DB_ID = DB_ID()
,@ObjectID = OBJECT_ID(QUOTENAME(DB_NAME(DB_ID()))+'.'+COALESCE(QUOTENAME(PARSENAME(@TableName,2)),'')+'.'+QUOTENAME(PARSENAME(@TableName,1)))
,@DatabaseName = QUOTENAME(DB_NAME(DB_ID()))
-- Obtain memory buffer information on database objects
IF OBJECT_ID('tempdb..#MemoryBuffer') IS NOT NULL
DROP TABLE #MemoryBuffer
CREATE TABLE #MemoryBuffer (
database_id INT
,object_id INT
,index_id INT
,partition_number INT
,buffered_page_count INT
,buffered_mb DECIMAL(12, 2)
)
SET @SQL = 'WITH AllocationUnits
AS (
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT p.object_id
,p.index_id
,p.partition_number
,au.allocation_unit_id
FROM '+@DatabaseName+'.sys.allocation_units AS au
INNER JOIN '+@DatabaseName+'.sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2
)
SELECT DB_ID()
,au.object_id
,au.index_id
,au.partition_number
,COUNT(*)AS buffered_page_count
,CONVERT(DECIMAL(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb
FROM '+@DatabaseName+'.sys.dm_os_buffer_descriptors AS bd
INNER JOIN AllocationUnits au ON bd.allocation_unit_id = au.allocation_unit_id
WHERE bd.database_id = db_id()
GROUP BY au.object_id, au.index_id, au.partition_number'
BEGIN TRY
INSERT INTO #MemoryBuffer
EXEC sys.sp_executesql @SQL
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #MemoryBuffer (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Obtain index meta data information
IF OBJECT_ID('tempdb..#IndexMeta') IS NOT NULL
DROP TABLE #IndexMeta
CREATE TABLE #IndexMeta (
database_id SMALLINT
,filegroup_name NVARCHAR(128)
,schema_id INT
,schema_name NVARCHAR(128)
,object_id INT
,table_name NVARCHAR(128)
,index_id INT
,index_name NVARCHAR(128)
,is_primary_key BIT
,is_unique BIT
,is_disabled BIT
,type_desc NVARCHAR(128)
,partition_number INT
,fill_factor TINYINT
,is_padded BIT
,reserved_page_count BIGINT
,size_in_mb DECIMAL(12, 2)
,row_count BIGINT
,filter_definition NVARCHAR(MAX)
,indexed_columns NVARCHAR(MAX)
,included_columns NVARCHAR(MAX)
,key_columns NVARCHAR(MAX)
,data_columns NVARCHAR(MAX)
,indexed_columns_ids NVARCHAR(1024)
,included_column_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT
database_id = DB_ID()
, filegroup = ds.name
, s.schema_id
, schema_name = s.name
, object_id = t.object_id
, table_name = t.name
, i.index_id
, index_name = COALESCE(i.name, ''N/A'')
, ps.partition_number
, i.is_primary_key
, i.is_unique
, i.is_disabled
, type_desc = CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc
, i.fill_factor
, i.is_padded
, ps.reserved_page_count
, size_in_mb = CAST(reserved_page_count * CAST(8 as float) / 1024 as DECIMAL(12,2))
, row_count
, i.filter_definition
, indexed_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, included_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 1
ORDER BY key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''')
, key_columns = STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ COALESCE((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),''''), 1, 2, '''')
, data_columns = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE STUFF((
SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), 1, 2, '''') END
, indexed_column_ids = (SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND is_included_column = 0
ORDER BY key_ordinal ASC
FOR XML PATH(''''))
+ ''|'' + COALESCE((SELECT QUOTENAME(CAST(ic.column_id AS VARCHAR(10))
+ CASE WHEN ic.is_descending_key = 0 THEN ''+'' ELSE ''-'' END, ''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id
AND c.column_id = ic_key.column_id
AND i.index_id = ic_key.index_id
AND ic_key.is_included_column = 0
WHERE i.object_id = ic.object_id
AND ic.index_id = 1
AND ic.is_included_column = 0
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')),'''')
+ CASE WHEN i.is_unique = 1 THEN ''U'' ELSE '''' END
, included_column_ids = CASE WHEN i.index_id IN (0,1) THEN ''ALL-COLUMNS'' ELSE
COALESCE((SELECT QUOTENAME(ic.column_id,''('')
FROM '+@DatabaseName+'.sys.index_columns ic
INNER JOIN '+@DatabaseName+'.sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN '+@DatabaseName+'.sys.index_columns ic_key ON c.object_id = ic_key.object_id AND c.column_id = ic_key.column_id AND ic_key.index_id = 1
WHERE i.object_id = ic.object_id
AND i.index_id = ic.index_id
AND ic.is_included_column = 1
AND ic_key.index_id IS NULL
ORDER BY ic.key_ordinal ASC
FOR XML PATH('''')), SPACE(0)) END
FROM '+@DatabaseName+'.sys.tables t
INNER JOIN '+@DatabaseName+'.sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN '+@DatabaseName+'.sys.indexes i ON t.object_id = i.object_id
INNER JOIN '+@DatabaseName+'.sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN '+@DatabaseName+'.sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id'
IF @ObjectID IS NOT NULL
SET @SQL = @SQL + CHAR(13) + 'WHERE t.object_id = @ObjectID '
BEGIN TRY
INSERT INTO #IndexMeta (
database_id
,filegroup_name
,schema_id
,schema_name
,object_id
,table_name
,index_id
,index_name
,partition_number
,is_primary_key
,is_unique
,is_disabled
,type_desc
,fill_factor
,is_padded
,reserved_page_count
,size_in_mb
,row_count
,filter_definition
,indexed_columns
,included_columns
,key_columns
,data_columns
,indexed_columns_ids
,included_column_ids)
EXEC sys.sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #IndexMeta (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
IF OBJECT_ID('tempdb..#IndexStatistics') IS NOT NULL
DROP TABLE #IndexStatistics
SELECT IDENTITY(INT,1,1) AS row_id
,CAST('' AS VARCHAR(10)) AS index_action
,CAST('' AS VARCHAR(25)) AS index_pros
,CAST('' AS VARCHAR(25)) AS index_cons
,im.database_id
,im.filegroup_name
,im.schema_id
,im.schema_name
,im.object_id
,im.table_name
,im.index_id
,im.index_name
,im.is_primary_key
,im.is_unique
,im.is_disabled
,CAST(0 AS BIT) AS has_unique
,im.type_desc
,im.partition_number
,im.fill_factor
,im.is_padded
,im.reserved_page_count
,im.size_in_mb
,mb.buffered_page_count
,mb.buffered_mb
,CAST(0 AS INT) AS table_buffered_mb
,CAST(100.*mb.buffered_page_count/NULLIF(im.reserved_page_count,0) AS DECIMAL(12,2)) AS buffered_percent
,im.row_count
,ROW_NUMBER() OVER (PARTITION BY im.object_id ORDER BY im.is_primary_key desc,ius.user_seeks + ius.user_scans + ius.user_lookups DESC) AS index_rank
, ius.user_seeks + ius.user_scans + ius.user_lookups AS user_total
, COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)
/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)
OVER(PARTITION BY im.object_id), 0) * 1.) as DECIMAL(6,2)),0) AS user_total_pct
,CAST(0 AS DECIMAL(6,2)) AS estimated_user_total_pct
,CAST(0 AS FLOAT) AS missing_index_impact -- Dick Baker 201303 (INT range not big enough and is f.p. anyway)
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
,ius.user_updates
,(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups))/NULLIF(ius.user_updates,0) AS read_to_update_ratio
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups >= ius.user_updates
THEN CEILING(1.*(ius.user_seeks + ius.user_scans + ius.user_lookups)/COALESCE(NULLIF(ius.user_seeks,0),1))
ELSE 0 END AS read_to_update
,CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups <= ius.user_updates
THEN CEILING(1.*(ius.user_updates)/COALESCE(NULLIF(ius.user_seeks + ius.user_scans + ius.user_lookups,0),1))
ELSE 0 END AS update_to_read
,ios.row_lock_count
,ios.row_lock_wait_count
,ios.row_lock_wait_in_ms
,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count,0) AS DECIMAL(12,2)) AS row_block_pct
,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count,0) AS DECIMAL(12,2)) AS avg_row_lock_waits_ms
,ios.page_latch_wait_count
,CAST(1. * page_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_latch_wait_ms
,ios.page_io_latch_wait_count
,CAST(1. * ios.page_io_latch_wait_in_ms / NULLIF(ios.page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_page_io_latch_wait_ms
,ios.tree_page_latch_wait_count AS tree_page_latch_wait_count
,CAST(1. * tree_page_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_latch_wait_ms
,ios.tree_page_io_latch_wait_count
,CAST(1. * ios.tree_page_io_latch_wait_in_ms / NULLIF(ios.tree_page_io_latch_wait_count,0) AS DECIMAL(12,2)) AS avg_tree_page_io_latch_wait_ms
,range_scan_count + singleton_lookup_count AS read_operations
,ios.leaf_insert_count + ios.leaf_update_count + ios.leaf_delete_count + ios.leaf_ghost_count AS leaf_writes
,leaf_allocation_count As leaf_page_allocations
,ios.leaf_page_merge_count AS leaf_page_merges
,ios.nonleaf_insert_count + ios.nonleaf_update_count + ios.nonleaf_delete_count AS nonleaf_writes
,ios.nonleaf_allocation_count AS nonleaf_page_allocations
,ios.nonleaf_page_merge_count AS nonleaf_page_merges
,im.indexed_columns
,im.included_columns
,im.filter_definition
,key_columns
,data_columns
,im.indexed_columns_ids
,im.included_column_ids
,CAST('' AS VARCHAR(MAX)) AS duplicate_indexes
,CAST('' AS SMALLINT) AS first_dup_index_id
,CAST('' AS VARCHAR(MAX)) AS overlapping_indexes
,CAST('' AS VARCHAR(MAX)) AS related_foreign_keys
,CAST('' AS XML) AS related_foreign_keys_xml
INTO #IndexStatistics
FROM #IndexMeta im
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON im.object_id = ius.object_id AND im.index_id = ius.index_id AND im.database_id = ius.database_id
LEFT OUTER JOIN sys.dm_db_index_operational_stats(@DB_ID, NULL, NULL, NULL) ios ON im.object_id = ios.object_id AND im.index_id = ios.index_id AND im.partition_number = ios.partition_number
LEFT OUTER JOIN #MemoryBuffer mb ON im.object_id = mb.object_id AND im.index_id = mb.index_id AND im.partition_number = mb.partition_number
IF @IncludeMissingIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, missing_index_impact, index_rank, user_total, user_seeks, user_scans, user_lookups, indexed_columns, included_columns)
SELECT
'' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(mid.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(mid.object_id) AS schema_name
,mid.object_id
,OBJECT_NAME(mid.object_id) AS table_name
,'--MISSING INDEX--' AS index_name
,'NONCLUSTERED' AS type_desc
,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact
,0 AS index_rank
,migs.user_seeks + migs.user_scans as user_total
,migs.user_seeks
,migs.user_scans
,0 as user_lookups
,COALESCE(equality_columns + CASE WHEN inequality_columns IS NOT NULL THEN ', ' ELSE SPACE(0) END, SPACE(0)) + COALESCE(inequality_columns, SPACE(0)) as indexed_columns
,included_columns
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = @DB_ID
END
-- Collect foreign key information.
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
DROP TABLE #ForeignKeys
CREATE TABLE #ForeignKeys
(
foreign_key_name NVARCHAR(256)
,object_id INT
,fk_columns NVARCHAR(max)
,fk_columns_ids NVARCHAR(1024)
)
SET @SQL = N'SELECT fk.name + ''|PARENT'' AS foreign_key_name
,fkc.parent_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.parent_object_id = c.object_id AND ifkc.parent_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.parent_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.parent_object_id = @ObjectID OR @ObjectID IS NULL)
UNION ALL
SELECT fk.name + ''|REFERENCED'' as foreign_key_name
,fkc.referenced_object_id AS object_id
,STUFF((SELECT '', '' + QUOTENAME(c.name)
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
INNER JOIN '+@DatabaseName+'.sys.columns c ON ifkc.referenced_object_id = c.object_id AND ifkc.referenced_column_id = c.column_id
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')), 1, 2, '''') AS fk_columns
,(SELECT QUOTENAME(CAST(ifkc.referenced_column_id AS VARCHAR(10))+''+'',''('')
FROM '+@DatabaseName+'.sys.foreign_key_columns ifkc
WHERE fk.object_id = ifkc.constraint_object_id
ORDER BY ifkc.constraint_column_id
FOR XML PATH('''')) AS fk_columns_compare
FROM '+@DatabaseName+'.sys.foreign_keys fk
INNER JOIN '+@DatabaseName+'.sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_column_id = 1
AND (fkc.referenced_object_id = @ObjectID OR @ObjectID IS NULL)'
BEGIN TRY
INSERT INTO #ForeignKeys
(foreign_key_name, object_id, fk_columns, fk_columns_ids)
EXEC sp_executesql @SQL, N'@DB_ID INT, @ObjectID INT', @DB_ID = @DB_ID, @ObjectID = @ObjectID
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Populate #ForeignKeys (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
-- Determine duplicate, overlapping, and foreign key index information
UPDATE i
SET duplicate_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids
FOR XML PATH('')), 1, 2, '')
,first_dup_index_id = (SELECT MIN(index_id)
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id > iibl.index_id
AND REPLACE(i.indexed_columns_ids,'|','') = REPLACE(iibl.indexed_columns_ids,'|','')
AND i.included_column_ids = iibl.included_column_ids)
,overlapping_indexes = STUFF((SELECT ', ' + index_name AS [data()]
FROM #IndexStatistics iibl
WHERE i.object_id = iibl.object_id
AND i.is_primary_key = iibl.is_primary_key
AND i.is_unique = iibl.is_unique
AND ISNULL(i.filter_definition,'') = ISNULL(iibl.filter_definition,'')
AND i.index_id <> iibl.index_id
AND LEFT(i.indexed_columns_ids, CHARINDEX('|',iibl.indexed_columns_ids,1)-1)
LIKE LEFT(iibl.indexed_columns_ids, CHARINDEX('|',i.indexed_columns_ids,1)-1) + '%'
AND Replace(i.indexed_columns_ids,'|','') <> Replace(iibl.indexed_columns_ids,'|','')
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys = STUFF((SELECT ', ' + foreign_key_name AS [data()]
FROM #ForeignKeys ifk
WHERE ifk.object_id = i.object_id
AND i.indexed_columns_ids LIKE ifk.fk_columns_ids + '%'
FOR XML PATH('')), 1, 2, '')
,related_foreign_keys_xml = CAST((SELECT foreign_key_name
FROM #ForeignKeys fk
WHERE fk.object_id = i.object_id
AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
FOR XML AUTO) as xml)
FROM #IndexStatistics i
IF @IncludeMissingFKIndexes = 1
BEGIN
INSERT INTO #IndexStatistics
(filegroup_name, schema_id, schema_name, object_id, table_name, index_name, type_desc, index_rank, indexed_columns, related_foreign_keys)
SELECT '' AS filegroup_name
,SCHEMA_ID(OBJECT_SCHEMA_NAME(fk.object_id)) AS schema_id
,OBJECT_SCHEMA_NAME(fk.object_id) AS schema_name
,fk.object_id
,OBJECT_NAME(fk.object_id) AS table_name
,'--MISSING FOREIGN KEY--' AS index_name
,'NONCLUSTERED' AS type_desc
,9999
,fk.fk_columns
,fk.foreign_key_name
FROM #ForeignKeys fk
LEFT OUTER JOIN #IndexStatistics i ON fk.object_id = i.object_id AND i.indexed_columns_ids LIKE fk.fk_columns_ids + '%'
WHERE i.index_name IS NULL
END
-- Determine whether tables have unique indexes
UPDATE i
SET has_unique = 1
FROM #IndexStatistics i
WHERE EXISTS (SELECT * FROM #IndexStatistics ii WHERE i.object_id = ii.object_id AND ii.is_unique = 1)
-- Calculate estimated user total for each index.
;WITH Aggregation
AS (
SELECT row_id
,CAST(100. * (user_seeks + user_scans + user_lookups)
/(NULLIF(SUM(user_seeks + user_scans + user_lookups)
OVER(PARTITION BY schema_name, table_name), 0) * 1.) as DECIMAL(12,2)) AS estimated_user_total_pct
,SUM(buffered_mb) OVER(PARTITION BY schema_name, table_name) as table_buffered_mb
FROM #IndexStatistics
)
UPDATE ibl
SET estimated_user_total_pct = COALESCE(a.estimated_user_total_pct, 0)
,table_buffered_mb = a.table_buffered_mb
FROM #IndexStatistics ibl
INNER JOIN Aggregation a ON ibl.row_id = a.row_id
-- Update Index Action information
;WITH IndexAction
AS (
SELECT row_id
,CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'
WHEN is_disabled = 1 THEN 'ENABLE'
WHEN duplicate_indexes IS NOT NULL AND first_dup_index_id IS NOT NULL AND index_id IS NOT NULL THEN 'DROP-DUP'
WHEN type_desc = '--MISSING FOREIGN KEY--' THEN 'CREATE'
WHEN type_desc = 'XML' THEN '---'
WHEN is_unique = 1 THEN '---'
WHEN related_foreign_keys IS NOT NULL THEN '---'
WHEN type_desc = '--NONCLUSTERED--' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc) <= 10 AND estimated_user_total_pct > 1 THEN 'CREATE'
WHEN type_desc = '--NONCLUSTERED--' THEN 'BLEND'
WHEN ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total desc, index_rank) > 10 AND index_id IS NOT NULL THEN 'DROP-COUNT'
WHEN index_id NOT IN (0,1) AND duplicate_indexes IS NULL AND user_total = 0 AND index_id IS NOT NULL
THEN 'DROP-USAGE'
ELSE '---' END AS index_action
FROM #IndexStatistics
)
UPDATE ibl
SET index_action = ia.index_action
FROM #IndexStatistics ibl INNER JOIN IndexAction ia
ON ibl.row_id = ia.row_id
-- Update Pro/Con statuses
UPDATE #IndexStatistics
SET index_pros = COALESCE(STUFF(CASE WHEN related_foreign_keys IS NOT NULL THEN ', FK' ELSE '' END
+ CASE WHEN is_unique = 1 THEN ', UQ' ELSE '' END
+ COALESCE(', ' + CASE WHEN read_to_update BETWEEN 1 AND 9 THEN '$'
WHEN read_to_update BETWEEN 10 AND 99 THEN '$$'
WHEN read_to_update BETWEEN 100 AND 999 THEN '$$$'
WHEN read_to_update > 999 THEN '$$$+' END, '')
,1,2,''),'')
,index_cons = COALESCE(STUFF(CASE WHEN user_seeks / NULLIF(user_scans,0) < 1000 THEN ', SCN' ELSE '' END
+ CASE WHEN duplicate_indexes IS NOT NULL THEN ', DP' ELSE '' END
+ CASE WHEN overlapping_indexes IS NOT NULL THEN ', OV' ELSE '' END
+ COALESCE(', ' + CASE WHEN update_to_read BETWEEN 1 AND 9 THEN '$'
WHEN update_to_read BETWEEN 10 AND 99 THEN '$$'
WHEN update_to_read BETWEEN 100 AND 999 THEN '$$$'
WHEN update_to_read > 999 THEN '$$$+' END, '')
+ CASE WHEN is_disabled = 1 THEN ', DSB' ELSE '' END
,1,2,''),'')
IF @Output = 'DETAILED'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, filter_definition
, is_primary_key
, is_unique
, is_disabled
, has_unique
, partition_number
, fill_factor
, is_padded
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, estimated_user_total_pct
, missing_index_impact
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_to_update_ratio
, read_to_update
, update_to_read
, row_lock_count
, row_lock_wait_count
, row_lock_wait_in_ms
, row_block_pct
, avg_row_lock_waits_ms
, page_latch_wait_count
, avg_page_latch_wait_ms
, page_io_latch_wait_count
, avg_page_io_latch_wait_ms
, tree_page_latch_wait_count
, avg_tree_page_latch_wait_ms
, tree_page_io_latch_wait_count
, avg_tree_page_io_latch_wait_ms
, read_operations
, leaf_writes
, leaf_page_allocations
, leaf_page_merges
, nonleaf_writes
, nonleaf_page_allocations
, nonleaf_page_merges
, duplicate_indexes
, overlapping_indexes
, related_foreign_keys
, related_foreign_keys_xml
, key_columns
, data_columns
FROM #IndexStatistics
WHERE (estimated_user_total_pct > 0.01 AND index_id IS NULL)
OR related_foreign_keys IS NOT NULL
OR index_id IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, COALESCE(user_total,-1) DESC, COALESCE(user_updates,-1) DESC, COALESCE(index_id,999)
END
ELSE IF @Output = 'DUPLICATE'
BEGIN
SELECT
DENSE_RANK() OVER (ORDER BY key_columns, data_columns) AS duplicate_group
, index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, duplicate_indexes
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE duplicate_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, RANK() OVER (ORDER BY key_columns, data_columns)
END
ELSE IF @Output = 'OVERLAPPING'
BEGIN
SELECT
index_action
, index_pros
, index_cons
, QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) as object_name
, overlapping_indexes
, index_name
, type_desc
, indexed_columns
, included_columns
, is_primary_key
, is_unique
, size_in_mb
, buffered_mb
, table_buffered_mb
, buffered_percent
, row_count
, user_total_pct
, user_total
, user_seeks
, user_scans
, user_lookups
, user_updates
, read_operations
FROM #IndexStatistics
WHERE overlapping_indexes IS NOT NULL
ORDER BY table_buffered_mb DESC, object_id, user_total DESC
END
END TRY
BEGIN CATCH
SELECT @ERROR_MESSAGE = 'Procedure Error (Line '+CAST(ERROR_LINE() AS NVARCHAR(25))+'): ' + ERROR_MESSAGE()
,@ERROR_SEVERITY = ERROR_SEVERITY()
,@ERROR_STATE = ERROR_STATE()
RAISERROR(@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH
GO
USE AdventureWorks2012
GO
EXEC dbo.sp_IndexAnalysis @Output = 'DETAILED'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
EXEC dbo.sp_IndexAnalysis @Output = 'OVERLAPPING'
,@TableName = '[Production].[ProductDescription]'
,@IncludeMissingIndexes = 1
,@IncludeMissingFKIndexes = 1
No comments:
Post a Comment