Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,
                sic.Index_ID,
                sis.user_seeks,
                sis.user_scans,
                sis.user_lookups,
                sis.user_updates
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.

share|improve this question
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? –  ypercube May 7 '12 at 12:27
3  
Use the Index, Luke! is a much better starting place for what indexes to have in your database. –  ypercube May 7 '12 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). –  Jon of All Trades May 7 '12 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 '12 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 '12 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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