Home
About me and Nucleus
Partners
Links
Articles, tips etc.
My blog
Microsoft articles
Newsgroups
Course demo files
Sync logins
SMTP Alerter
I often find myself wanting to know lots about the indexes for a table or for all tables in a database. This is why I wrote sp_indexinfo.
Versions etc.
This script was written for SQL Server 2005 or later.
Article written: 2008-07-07
Article last modified: 2008-07-13
Details
I can't count how many times I wanted to know "more" about physical attributes for a table, including index information. So I end up running sp_helpindex, some SELECT over sys.indexes, some reports in SSMS, some other queries against other catalog and dynamic management views, etc. I wrote this procedure for those cases. Here's my version of a procedure to give me information like:
- What indexes exists for a particular table or for each table in the database
- Clustered, non-clustered or heap
- Columns in the index
- Included columns in the index
- Unique or nonunique
- Number rows in the table
- Space usage
- How frequently the indexes has been used
- Any obvious indexes I should add?
Tip
I frequently spend lots of time in one particular database, and I then would want to "post-process" the result from a procedure like this. By "post-process" I mean things like sorting and grouping. I suggest that you for those cases take the first SELECT statement and create a view out of it. You can now SELECT from this view with any ORDER BY, GROUP BY, aggregate function etc as you wish. Start with the row starting with WITH and end before the "AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat" row. For instance, I created such a view (I named the view index_info) in one of my databases and below are some queries I ran against this view:
SELECT * FROM index_info
ORDER BY MB DESC
SELECT * FROM index_info
WHERE table_name = 'tblname'
SELECT table_name, SUM(MB) AS size
FROM index_info
GROUP BY table_name
ORDER BY size DESC
Considerations
- You should practice caution when interpreting the results. For instance, a missing index reported doesn't meccesarily mean that you should go and create this index. Make sure you understand the information and where it is comming from (read the source code from the proc). I've only used documented sources - Books Online is your friend.
- In order for this to be treated as a regular system procedure we need to create it in the master database and also mark it as a system procedure. Marking your own procedures as system procedures is not supported so do this at your own risk!!!
Parameters:
@tblPat sysname = '%'
Name of table. Default is all tables in current database.
@missing_ix tinyint = 1
Whether or not to include missing indexes information. Default is "yes".
Usage examples
USE Adventureworks
EXEC sp_indexinfo
EXEC sp_indexinfo 'Product'
EXEC sp_indexinfo 'Product', 0
EXEC sp_indexinfo 'Product%'
EXEC sp_indexinfo DEFAULT, 0
EXEC pubs..sp_indexinfo
The code:
USE master
GO
IF OBJECT_ID('sp_indexinfo') IS NOT NULL DROP PROC sp_indexinfo
GO
CREATE PROCEDURE sp_IndexInfo
@tblPat sysname = '%'
,@missing_ix tinyint = 1
AS
--Written by Tibor Karaszi 2008-07-07
--Last modified by Tibor Karaszi 2008-07-10
WITH key_columns AS
(
SELECT c.OBJECT_ID, c.name AS column_name, ic.key_ordinal, ic.is_included_column, ic.index_id, ic.is_descending_key
FROM sys.columns AS c
INNER JOIN sys.index_columns AS ic ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id
)
, physical_info AS
(
SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
GROUP BY OBJECT_ID, index_id, ds.name
)
SELECT
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name
,OBJECT_NAME(i.OBJECT_ID) AS table_name
,i.name AS index_name
,CASE i.type WHEN 0 THEN 'heap' WHEN 1 THEN 'cl' WHEN 2 THEN 'nc' WHEN 3 THEN 'xml' ELSE CAST(i.type AS VARCHAR(2)) END AS type
,i.is_unique
,CASE
WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN 'no'
WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN 'PK'
WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN 'UQ'
END
AS cnstr
,(SELECT
CAST(kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '' ELSE ' DESC' END AS VARCHAR(MAX)) + ', ' AS [text()]
FROM key_columns AS kc
WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0
ORDER BY key_ordinal
FOR XML PATH('')
) AS key_columns
,(SELECT CAST(column_name AS VARCHAR(MAX)) + ', ' AS [text()]
FROM key_columns AS kc
WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1
ORDER BY key_ordinal
FOR XML PATH('')
) AS included_columns
,p.location
,p.rows
,p.pages
,CAST((p.pages * 8.00) / 1024 AS decimal(9,2)) AS MB
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
FROM sys.indexes AS i
INNER JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY table_name, index_name
IF @missing_ix = 1
BEGIN
SELECT
OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
,OBJECT_NAME(d.OBJECT_ID) AS table_name
,'CREATE INDEX <IndexName> ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' '
+ '(' + COALESCE(d.equality_columns + COALESCE(', ' + d.inequality_columns, ''), d.inequality_columns) + ')'
+ COALESCE(' INCLUDE(' + d.included_columns + ')', '')
AS ddl
,s.user_seeks
,s.user_scans
,s.avg_user_impact
FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
AND d.database_id = DB_ID()
ORDER BY avg_user_impact DESC
END
GO
EXEC sp_MS_Marksystemobject sp_IndexInfo
Change log
2008-07-08:
Added missing index information controlled by optional parameter.
Added schema name in first resultset.
2008-07-09:
Added 'DESC' for descending keys, tip from Alejandro Mesa.
Added COALESCE for included columns so the whole CREATE INDEX isn't NULL for missing index recommendation where no included columns are recommended. Caught by Aaron Bertrand.
Restricted missing index recommendation to current database. Caught by Aaron Bertrand.
Added parenthesis around key columns in the generated CREATE INDEX command, in the missing indexes resultset.
2008-07-13:
Fixed so that a comma is introuced in the generated CREATE TABLE if both equality as well as inequality columns are recommended. Thanks to Razvan Socol.