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.
