In a SQL Server and an Entity Framework based PMS application, we have a table that stores data of Postings (for invoices). One of the columns in this table is the following:
[PostingDate] [datetime] NOT NULL
It has no triggers or constraints or anything else, except for an index that is created extremely simply, like the following:
CREATE NONCLUSTERED INDEX PostingDateIndex
ON dbo.Posting(PostingDate)
For some reason queries against this index randomly become slow, about twice per month, while the server of the application is 24/7 online (not even reboots) and – being a PMS – is used frequently through day and night.
DBCC CHECKTABLE('dbo.Posting')
does not find any errors when the queries slow down. The only symptoms are that queries that heavily rely on filtering rows by this PostingDate column start getting horribly slow, they usually go out of the 10min timeout our queries have (when the index is okay they take less than 30sec to complete).
The dbo.Posting table itself still remains useful, you can insert and edit rows, you can also make simple selects, whatever you want, but when you start queries that are heavily dependent on PostingDate, they just run infinitely.
The database has over a dozen other indexes (one of them is on another, int type column of this very same table) but none of them encounters this issue. Also the issue randomly occurs both with SQL Server 2014 and SQL Server 2016.
To fix it all we have to do is right click on the index in Management Studio and select Rebuild. Once that is done everything runs quickly again… however the issue randomly comes back and it's not really acceptable in a production environment that some of the queries just die until we manually rebuild an index.
I have absolutely no idea what could cause this issue and why only this one specific index is affected. Could anyone point me in the right direction?
How do I even find what causes it?
Best Answer
I don't think this corruption. It sounds to me like out-of-date statistics are resulting in bad Execution Plans. When you rebuild the index the statistics are implicitly updated which is temporarily fixing your issue but not for the reason you think. You should have a strategy for updating Statistics (and re-organising/rebuilding indices).
We use Ola Hallengen's index maintenance scripts.