Solving performance problems after large data change occurs

If you’ve worked with SQL Server for any period of time you’ve problem run into what appear to be pretty random occurrences of SQL Server starting to have performance problems which go away after you rebuild the indexes. What’s going on to make the problem happen, and what’s solving the problem are however not what they appear.

When this happens the common thought it that it’s index fragmentation that is causing the problem. And while that isn’t helping you, it isn’t the root cause of the issue.

In most situations, however the cause of the problem is out of date statistics. And the solution is to update the statistics, or more specifically have SQL Server update them more often automatically.

If you are running SQL Server 2008 R2 SP1 through SQL Server 2016 then you want to turn on trace flag 2371 when SQL Server status (you can set it as a startup parameter, and then turn it on by running DBCC TRACEON (2371, -1) and that’ll make it take effect until the next restart).  There’s really no downside to this trace flag being on, especially for systems that have large amounts of data change impacting query performance. The only negative impact that this trace flag will have is that the system will trigger update statistics more often throughout the day. To ensure that this doesn’t cause problems we also need to enable async auto stats updates.

After you’ve made these changes, you’ll need to update statistics as update stats won’t trigger right away, and the problem shouldn’t return.

Denny

The post Solving performance problems after large data change occurs appeared first on SQL Server with Mr. Denny.

Share

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?