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.