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.
Best Answer
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:
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