August 9, 2016 at 3:08 pm
Hello,
I'm going to start this out with the fact that we don't have a proper SQL Server DBA on site. I've got a few years of experience administrating but nothing when it comes to some of the heavier troubleshooting requirements.
We've been having some issues with our ERP database basically the system is crashing when users are working with a couple tables and queries taking forever to return. It's taken months for the vendor support to get back to us with a solution. What they found was that running SP_UPDATESTATS resolves issue. We tested it on one of our development system with the queries at least I'm still testing the other issue.
I'm not one to run things blind and researched it be for we ran it. Now I'm aware that we should have been monitoring our statistics and based on the recommendations we should only run it for single tables instead of the whole database.
There are some articles that advise against running this procedure during normal times to prevent having an impact on production activities. Can anyone tell me how much truth there is to that statement?
Also if there's any suggestions on better ways to maintain and update the statistics I'd really appreciate it.
August 9, 2016 at 3:59 pm
I wouldn't recommend running that during peak time. To be honest, I wouldn't recommend running that specific stored proc either, there are better ways to update stats (the explicit UPDATE STATISTICS ... gives you control over sampling and which tables get their stats updated, whereas updatestats doesn't)
The larger problem here is that the vendor has likely identified a fix for the symptoms, not the root problem if they're recommending running that any time you see the problem, painkiller for a broken arm if you will.
Do you have budget to get a SQL consultant in to do some performance tuning?
As for general stats maintenance, I like Ola Hallengren's index and stats maintenance. https://ola.hallengren.com/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2016 at 4:13 pm
GilaMonster (8/9/2016)
I wouldn't recommend running that during peak time. To be honest, I wouldn't recommend running that specific stored proc either, there are better ways to update stats (the explicit UPDATE STATISTICS ... gives you control over sampling and which tables get their stats updated, whereas updatestats doesn't)The larger problem here is that the vendor has likely identified a fix for the symptoms, not the root problem if they're recommending running that any time you see the problem, painkiller for a broken arm if you will.
Do you have budget to get a SQL consultant in to do some performance tuning?
As for general stats maintenance, I like Ola Hallengren's index and stats maintenance. https://ola.hallengren.com/
That's kind of in line with what I found looking into it. Is it something that we could run during a slow time or does it warrant waiting until our next scheduled maintenance?
I absolutely agree with you in principle. Hate running blanket statements like this when we can do something specific. The trouble is that when we tried to run it for the specific tables used it had no effect, they won't exactly tell us which tables it's hit. The other problem is that when I ran a query to show the status of the statistics it showed that more than a few hadn't been updated in several years. When we tested it on our dev system there were performance increases across the board.
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.
Unfortunately I highly doubt we have the money for a consultant like that, likewise with our ERP much of what is actually being run against the database and the table/index/relationship structure are off limits per our support agreement.
Thanks for the link. I'm going to give that a read and see if I find anything useful.
August 9, 2016 at 4:18 pm
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2016 at 9:06 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply