Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get Index Details

We all know that indexes on tables help performance. However, sometimes too much indexing could affect performance as well. One way to see some details of indexes for a table is by querying the sys.dm_db_partition_stats and the sys.indexes table. As you can see by the below, I have used the OBJECT_NAME function (passing in the Object_id) to retrieve details about a specified object. In this case, 'DimInvoiceDetail'. Go ahead and test it out, but be sure to change the 'DimInvoiceDetail' out with one of your tables that you want to see the indexes on.

Script 1: A Quick Script

SELECT

      OBJECT_NAME(p.object_id)

      , i.name

      , p.*

FROM

      sys.dm_db_partition_stats p

      JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

WHERE

      OBJECT_NAME(p.object_id) = 'DimInvoiceDetail'

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.