Is Index causing timeouts? 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.
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