8 Weeks of Indexes: Determining what you have now.

This is post #5 of my 8 Weeks of Indexes; these are not posted weekly; but I am getting faster at posting them. (Side note:  I am making it a personal effort to use time at work [hey it’s learning right] to write my blogs)

When you are given access to a database and are told to manage it; what is the best way to figure out what indexes are available, are they being used, and how big are they?

Let us figure this out step by step.

What Indexes do I have?

Any time I have to determine what indexes a database has, I always use the following script. It is quick, easy and returns most of the information you need to identify an index and it’s configuration.

USE AdventureWorks2012;




   Object_Name(i.object_id) AS [table]



   ,(p.used_page_count * 8) AS SizeKB














FROM sys.indexes i

INNER JOIN sys.data_spaces ds

   ON ds.data_space_id = i.data_space_id

INNER JOIN sys.dm_db_partition_stats p

   ON p.object_id = i.object_id

   AND p.index_id = i.index_id

WHERE ObjectProperty(i.object_id,'IsUserTable')=1


The below picture is a sample of the results for the above query.


Next, I usually want to know IF the indexes are actually being used. Believe it or not; there are sometimes indexes placed on databases that never get used.  And that is just a waste of I/O and disk space.


      db_name(s.database_id) AS [Database]

      ,object_name(s.object_id) as [Table]

      ,i.name + ' (' + Cast(i.index_id AS VARCHAR(10)) + ')' AS [IndexName]

      ,ds.name [FileGroup]


      ,SUM(s.user_seeks) as [user_seeks (good)]

      ,SUM(s.user_scans) as [user_scans (bad)]

      ,SUM(s.user_lookups) as [user_lookups (worse)]

      ,SUM(s.user_seeks + user_scans + user_lookups)AS [reads], 

      SUM(s.user_updates) AS [writes],



      STATS_DATE(i.object_id, i.index_id) AS statistics_update_date

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

   ON s.[object_id] = i.[object_id]

   AND i.index_id = s.index_id

INNER JOIN sys.data_spaces ds

   ON i.data_space_id=ds.data_space_id

WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

   AND s.database_id = DB_ID()

GROUP BY db_name(s.database_id),ds.name, OBJECT_NAME(s.[object_id]), i.name, i.fill_factor


ORDER BY i.index_id;

This query provides the information you need to determine how often the indexes are used and if the SQL Engine is using SEEK, SCAN or LOOKUP to find data (this will be discussed in my next article).  If the index is not listed with these results; it has not been used since last index rebuild or SQL Engine restart.

And that is how I determine what indexes a server has, how they are being used and how big they are.

Next week (hopefully), I will go into how to determine if the indexes are effective!


Posted on August 21, 2015, in Indexes and tagged . Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code


To Catch A Falling Cactus


Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

%d bloggers like this: