GilaMonster (8/9/2016)
Erik de Jonge (8/9/2016)When we tested it on our dev system there were performance increases across the board.
Yup, not a surprise there, You do need some index/stats maintenance. Without it you will have generally poor performance.
What I'm going to be pushing for is that we monitor the statistics moving forward and only update the tables that need it. Also why I'm curious if there are good ways to keep an eye on it. Where we're at right now things have been left for so long that we really need to clean house.
Use Ola's maintenance routines and see if that helps. You can easily run that daily if you're not a 24/7 shop. It incorporates the monitoring that you're talking about, it'll only update stats and rebuild indexes that need it.
If you are still seeing performance drops mid-day, it probably means it's not stats that are the problem. The thing is, updating stats invalidates cached plans and if there was a poor plan in cache, it appears that the stats update was the fix.
I'll give his routines a read through and see what we can do. We are a 24/7 shop so we're going to make sure anything we do doesn't have a significant impact.
Fortunately it's not a drop in the middle of the day or anything like that. This was a cumulative problem, it got lower over time until we got to the point where the client application was crashing and we had to get the vendor involved.