0

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?

  • 2
    Indexes don't go nuts. SQL Server doesn't do things at random. Corruption is a big word you shouldn't toss around lightly. The three topics you should research are histogram statistics, fragmentation and query plans. – Jeroen Mostert Jan 23 '17 at 10:08
  • 1
    Sounds like you're not updating statistics regularly. Is this the case? – James Anderson Jan 23 '17 at 10:17
  • I guess so, since I've never heard about them... Whenever I used indexes before, they just worked "out of the box" and didn't require maintenance. Then again, I'm just a C# developer and haven't bothered very much with databases. Anyways thanks for these tips guys! – Tyrel Jan 23 '17 at 10:21
  • Index maintenance for big tables that are frequently modified in particular hotspots is one area where SQL Server doesn't do so well with making things run "out of the box". We have an OLTP table where updating statistics takes place every 10 minutes, out of necessity. This is true even with the tweaks that were made to the thresholds for automatic updating. – Jeroen Mostert Jan 23 '17 at 10:26
5

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.

  • Hmm, this sounds intresting, thanks! Haven't really heard about statistics and them going out of date before. How often do you run these maintenance scripts? We have a nightly db backup, maybe we could run it at that time, or would that be too often? – Tyrel Jan 23 '17 at 10:23
  • 1
    @Tyrel How often do you fill your gas tank? It depends. I suspect most people schedule Ola's stuff nightly but there are all kinds of tweaks you can do to schedule, thresholds, etc. that will depend on the needs of your environment. – Aaron Bertrand Jan 23 '17 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.