Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often.

I am using the following query to keep tab on index stats

SELECT DISTINCT Object_name(sis.OBJECT_ID) TableName,
                si.name                    AS IndexName,
                sc.Name                    AS ColumnName,
FROM   sys.dm_db_index_usage_stats sis
       INNER JOIN sys.indexes si
         ON sis.OBJECT_ID = si.OBJECT_ID
            AND sis.Index_ID = si.Index_ID
       INNER JOIN sys.index_columns sic
         ON sis.OBJECT_ID = sic.OBJECT_ID
            AND sic.Index_ID = si.Index_ID
       INNER JOIN sys.columns sc
         ON sis.OBJECT_ID = sc.OBJECT_ID
            AND sic.Column_ID = sc.Column_ID
WHERE  sis.Database_ID = Db_id('mydb') 

Earlier, there was hardly any user_seeks and most of the queries did a user_scan. As I changed the indexes, the user scans has gone down to zero and all the queries are using user_seek. Now I have read many places that index seeks are desired over index scans, so with that thought my db perf should be better.

But I am getting a lot of SQL Timeouts on the Asp.NET web app. Given that the index I defined contains 11 columns and a larger number of included columns, I was wondering whether maintaining the index itself has affected the perf?

After creating the new index, the user seeks to user updates ratio is 4489587/111656.

  • 1
    Not enough info. Did you add an 11-column index? Did you replace previously used indexes with this 11-column index? What queries do you run against these tables? May 7, 2012 at 12:27
  • 3
    Use the Index, Luke! is a much better starting place for what indexes to have in your database. May 7, 2012 at 13:18
  • 1
    When several fields are involved, or the indices have low cardinality, a table scan can actually be faster. Thus, your 11-field index may not be helpful. Using the classic phone book metaphor, imagine if you were asked to look up all names including the letter "S" (not just the first letter). It would be faster to just go through it page by page (table scan) than to look up the thousands of page numbers with a hit and then go to each of those pages (index seek). May 7, 2012 at 23:37
  • 2
    As soon as the first column in your index is not used in your query, the index cannot be efficiently used. Also, maybe you deleted some indexes that were crucial for some queries that now time out. I once had better performance using single indexes for each query column instead of a large combined one (stackoverflow.com/q/702469/44522).
    – MicSim
    May 9, 2012 at 11:57
  • 2
    It looks like you've abandoned this question, but for better help, follow these guidelines when getting help with a slow query: brentozar.com/archive/2009/03/getting-help-with-a-slow-query
    – Brent Ozar
    May 16, 2012 at 9:05

1 Answer 1


You must get execution plan of each slowly query first, and survay required Index.

for change timeout time use below address in SSMS:

Tools\Options\Designers\Trasnaction time-out after

Default value for timeout is 30s and you change this value to about 6000s and then create own index. The above path is in SQL Server Management Studio 2008 R2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.