SQL Server Timeout: Drop and Recreate Index Solves the Issue

Question

After having sql timeout issues with one of our queries we added a few indices that then made the query run very quickly and got rid of any timeout out issues we were having. The strange thing is after about a month of use, the query starts to timeout again. If we drop the same set of indices and recreate them, the query starts running very quickly again.

The indices are nothing fancy:

CREATE NONCLUSTERED INDEX [IX_Transaction_BillingAccountId] ON [dbo].[Transaction] 
(
     [BillingAccountId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Any ideas as to why this would happen? We are using SQL Server 2008 Express.

Answer 1

There is possibility of fragmentation issue with the index.

Check possibility of index fragmentation -

Here is a simple query to check fragmentation on your existing indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count 
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats 
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY 
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their fragmentation in %.

Rebuilding of indexes will solve the problem.

Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.

In many environments special scripts will be written to handle this, for example:

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.

You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.

Reference link

Answer 2

It could be that your index is no longer being used by the SQL Server query plan generator. This can happen for a number of reasons. A big reason could be the row count in your table.

Have you generated a query plan in SSMS to see if the index is being used. And, if it is, whether a seek or scan is being done?

A rebuilding of the index may or may not help. But it wouldn't surprise me if it didn't. I suspect you've reached some sort of "tipping point" where SQL Server decided not to use the index. Looking at the query plan will help you with this.

Answer 3

Index Fragmentation and out of date statistics! Don't forget stats, it's one of the most important factors in SQL Server deciding whether or not to use a index.

Next time it runs slow, check out how fragmented the clustered and non clustered indexes are, and also check to see if you have AUTO_UPDATE Stats enabled!