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!


8 Weeks of Indexes: What is an Index?

First and foremost, I am a slacker!  It takes discipline to write a weekly blog, I am not sure how some people can do it daily!  However, I hope to get back on track for this series.

Merriam-Webster defines index as:

a list … arranged usually in alphabetical order of some specified datum

One of the most common real world examples of an Index is your telephone book. The book stores information (Name, address and phone number) sorted alphabetically by last name. The pages are written in such a way that A comes before B and B comes before C, etc.. If one knows their alphabet, then any name can be easily looked up.  Typically the first “key” to finding a name is at the top of the page, which tells you what section of the book you are in.  If you were to locate my entry in the phonebook, you would quickly scan through the key until you found the letter B at the top of the page.  Then you would continue to scan until you find the group of entries for BISHOP.  And of course, then locate which entry matched my name, BISHOP, ROBERT. If there were no key at the top of the page, you would have to seek through all the pages one at a time until you got to the B section.  Another excellent real-world example of an index system, is the Dewey Decimal System.  Libraries have been indexing their books with a numbering system for years. 

So, how does this all relate to SQL Server?  There are several bold print words above that translate to SQL Server terms and how SQL works the same way as a phone book.  To fully understand how SQL Indexes work one really needs to know how SQL stores data. We know SQL has the .mdf files that actually stores all your data.  However, the data file is made of pages that are 8 KB in size.  At the top of each page is a “page header” used to store system information about that page.  There are many different types of pages that store different things, but two specific types of pages I want to talk about are “data” pages and “index” pages.

A “data page” is where your actual data (based on data types) is stored and as you guessed it, the index page stores index information. The “key” to proper storage of data is a Clustered Index.  A Clustered Index, physically writes and stores a row of data in a page by selected column and by sort order selected.  So a Clustered Index on a user table could be by the column “Last Name”, just like a phone book. This will ensure that the data rows are written in alphabetical order on each page and in turn each page will be in alphabetical order as well, very efficient.  SQL Engine “scans” the index to determine exactly what page the “B” last names are located.  If a table did not have a clustered index, the data would be stored in a “first come-first served” fashion.  In this scenario, the SQL Engine would have to scan then entire page or multiple pages to find your entry, very inefficient.  Imagine how inefficient a phone book would be if the publisher just kept adding rows to the end of the book every year without being sorted by name.  How long would it take you to find my name then?

So, the key to storing data in SQL, is to have a pre-determined way you want the data rows saved to the page. Ideally this would be the most used method of finding a row, i.e. by “Last Name”. 

Next week…..Types of Indexes