THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Updated sp_indexinfo

It was time to give sp_indexinfo some love. The procedure is meant to be the "ultimate" index information procedure, providing lots of information about all indexes in a database or all indexes for a certain table. Here is what I did in this update:

  • Changed the second query that retrieves missing index information so it generates the index name (based on schema name, table name and column named - limited to 128 characters).
  • Re-arranged and shortened column names to make output more compact and more commonly used column moved to the right.
  • Uncommented some columns that were previously commented. (At least one, filter, has to be commented if you want to run this on 2005.)
  • Added support for columnstore indexes.
  • Decoded the type for columnstore indexes to col-store.

You find the procedure here. 

Published Thursday, May 22, 2014 8:09 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jon Morisi said:

Thanks!

May 22, 2014 5:57 PM
 

JH said:

Your procedure does not work.  It doesn't return any rows.

May 28, 2014 12:56 PM
 

TiborKaraszi said:

How do you call it? Note that you are only to provide table name (if any), not schema name. Like:

EXEC 'Products'

Not:

EXEC 'Production.Products'

May 28, 2014 12:58 PM
 

randy hartwig said:

Hi, is your link down?  I just saw this and would like to try your procedure but I keep getting 'no access'.

December 9, 2014 11:09 AM
 

TiborKaraszi said:

Hi Randy,

The link works for me. Perhaps you can find a cached page at Google?

http://webcache.googleusercontent.com/search?q=cache:www.karaszi.com/SQLServer/util_sp_indexinfo.asp

December 9, 2014 1:06 PM
 

David said:

--Reworked to allow for qualified object names (schema.table)

USE [master]

GO

CREATE PROCEDURE [dbo].[SP_Utility_IndexInfo]

   @Object SYSNAME = NULL -- Schema.Name of object

   , @ShowMissing TINYINT = 0        -- Show missing indexes

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET NOCOUNT ON;

DECLARE @Schema VARCHAR(255)

, @Table VARCHAR(255)

, @crlf CHAR(2) = CHAR(13) + CHAR(10)

IF @Object IS NULL

OR LEN(@Object) - LEN(REPLACE(@Object, '.', '')) > 1 -- More than one period

 BEGIN

RAISERROR('Please supply a target object name in format Schema.Object, and try again.  Example @Object = Person.Address', 16, 1)

RETURN

 END

SET @Object = REPLACE(REPLACE(@Object, ']', ''), '[', '') -- Remove brackets

--PRINT 'Object:  ' + @Object

IF CHARINDEX('.', @Object) = 0 -- If missing period

 BEGIN

SET @Schema = 'dbo'

SET @Table = LTRIM(RTRIM(@Object))

 END

ELSE

 BEGIN

SELECT @Schema = RTRIM(LTRIM(REPLACE(REPLACE(LEFT(@Object, CHARINDEX('.', @Object) - 1), ']', ''), '[', '')))

SELECT @Table = RTRIM(LTRIM(REVERSE(LEFT(REVERSE(@Object), CHARINDEX('.', REVERSE(@Object)) - 1))))

 END

--PRINT 'Schema:  ' + @Schema

--PRINT 'Table:   ' + @Table

;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(

CASE

WHEN a.type_desc = 'IN_ROW_DATA'

THEN p.[rows]

ELSE 0

END

) 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', OBJECT_NAME(i.OBJECT_ID) AS 'Table', i.name AS 'Index'

, CASE i.TYPE

WHEN 0

THEN 'Heap'

WHEN 1

THEN 'Clustered'

WHEN 2

THEN 'NonClustered'

WHEN 3

THEN 'XML'

WHEN 6

THEN 'ColumnStore'

ELSE CAST(i.TYPE AS VARCHAR(2))

 END AS 'Type'

, CASE i.is_unique

WHEN 1

THEN 'Yes'

WHEN 0

THEN 'No'

 END AS 'Unique'

, CASE

WHEN is_primary_key = 0

AND is_unique_constraint = 0

THEN ''

               WHEN is_primary_key = 1

AND is_unique_constraint = 0

THEN 'PrimaryKey'

               WHEN is_primary_key = 0

AND is_unique_constraint = 1

THEN 'Unique'

         END AS 'Constraint'

, STUFF(

(

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('')

), 1, 2, ''

) AS 'KeyColumns'

, STUFF(

(

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('')

), 1, 2, ''

) AS 'IncludedColumns'

, p.[rows] AS 'Rows'

, p.pages as 'Pages', CAST(

(p.pages * 8.00 ) / 1024 AS DECIMAL(9, 2)

 ) AS MB

, s.user_seeks AS 'Seeks', s.user_scans AS 'Scans', s.user_lookups AS 'Lookups', s.user_updates AS 'Updates'

, CASE

WHEN i.is_disabled = 1

THEN '[DISABLED]'

               ELSE p.location

         END AS 'Location'

, i.filter_definition AS 'Filter', INDEXPROPERTY(i.object_id, i.name, 'IsDisabled') AS 'Disabled'

, INDEXPROPERTY(i.object_id, i.name, 'IndexDepth') AS depth, INDEXPROPERTY(i.object_id, i.name, 'IndexFillFactor ') AS 'FillFactor '

, INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') AS page_lock_disallowed, INDEXPROPERTY(i.object_id, i.name, 'IsRowLockDisallowed') AS 'RowLocksDisabled'

FROM sys.indexes AS i

LEFT OUTER JOIN physical_info AS p

ON i.OBJECT_ID = p.OBJECT_ID

AND i.index_id = p.index_id

LEFT OUTER 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 OBJECTPROPERTY(i.OBJECT_ID, 'IsTableFunction') = 0

AND OBJECT_NAME(i.OBJECT_ID) = @Table

AND OBJECT_SCHEMA_NAME(i.OBJECT_ID) = @Schema

ORDER BY 'Table', 'Index'  

IF @ShowMissing = 1

 BEGIN

SELECT OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name, OBJECT_NAME(d.OBJECT_ID) AS 'Table'

, 'CREATE INDEX' + CAST(OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '__' + OBJECT_NAME(d.OBJECT_ID) + '__'

               + REPLACE(REPLACE(COALESCE(REPLACE(d.equality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__'

               + REPLACE(REPLACE(COALESCE(REPLACE(d.inequality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__'

               + REPLACE(REPLACE(COALESCE(REPLACE(d.included_columns, ', ', '__'), ''), '[', ''), ']', '') AS SYSNAME) + @crlf

               + 'ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' ' + '('

               + COALESCE(d.equality_columns + COALESCE(', ' + d.inequality_columns, ''), d.inequality_columns) + ')' + @crlf

               + COALESCE('INCLUDE(' + d.included_columns + ')', '') AS 'DDL', s.user_seeks AS 'Seeks', s.user_scans AS 'Scans', s.avg_user_impact AS 'AvgUserImpact'

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) = @Table

AND OBJECT_SCHEMA_NAME(d.OBJECT_ID) = @Schema

AND d.database_id = DB_ID()

ORDER BY 'AvgUserImpact' DESC

 END

GO  

EXEC sp_MS_Marksystemobject SP_Utility_IndexInfo

GO

April 1, 2015 10:53 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement