June 9, 2017 at 3:33 am
Hello all,
From our developers I get the signal that regenerating the statistics does improve performance significantly and that regenerating the statistics is needed before specific actions.
Although the amount of data is large, and there are changes in the data in general most data does not change.
My understanding is that statistics are maintained automatically and that there is no need to regenerate the statistics manually. And even that if the statistics are a few days (or even weeks) old, that this should not have the impact that the developers claim this has.
Is there any side effect that generating the statistics has, which might cause this behavior?
(For example clearing the cache or the plan cache).
What should I check for on the system where this occures, or any other suggestions ?
Thanks for your time and attention,
Ben
June 9, 2017 at 3:55 am
Statistics are maintained automatically, but that automatic maintenance may not be sufficient in some cases.
Stats that are a few weeks old on a static table are fine, the table is static, the data's not changing. Stats that are a few weeks old on a table that's constantly getting data modifications is not ok, and may result in really bad executoin plans and resulting poor performance,
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
June 9, 2017 at 4:36 am
GilaMonster - Friday, June 9, 2017 3:55 AMStats that are a few weeks old on a table that's constantly getting data modifications is not ok, and may result in really bad executoin plans and resulting poor performance,
Indeed, sometimes even a few days old
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2017 at 4:37 am
ben.brugman - Friday, June 9, 2017 3:33 AMHello all,From our developers I get the signal that regenerating the statistics does improve performance significantly and that regenerating the statistics is needed before specific actions.
Although the amount of data is large, and there are changes in the data in general most data does not change.
My understanding is that statistics are maintained automatically and that there is no need to regenerate the statistics manually. And even that if the statistics are a few days (or even weeks) old, that this should not have the impact that the developers claim this has.
Is there any side effect that generating the statistics has, which might cause this behavior?
(For example clearing the cache or the plan cache).What should I check for on the system where this occures, or any other suggestions ?
Thanks for your time and attention,
Ben
Can you provide more information on the table schema, size and general modifications (i.e. large number of inserts or updates, etc)?
you may be aflling victim to the new cardinality estimator in sql server 2014 and upwards. Do you have this enabled?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 9, 2017 at 5:54 am
Perry Whittle - Friday, June 9, 2017 4:36 AMGilaMonster - Friday, June 9, 2017 3:55 AMStats that are a few weeks old on a table that's constantly getting data modifications is not ok, and may result in really bad executoin plans and resulting poor performance,Indeed, sometimes even a few days old
I've heard of an extreme case where stats updates were needed every 4 hours on a particular table. Not, fortunately, something I've encountered myself.
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
June 9, 2017 at 6:46 am
On the site:
https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics
"
Consider updating statistics for the following conditions:
Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs.
"
Would it be a good idee to use trace flag 2371 ?
See: https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server
Or is this something which is not applicable in most situations?
Still suprised that SQL-server 'fails' at something so basic. 😉
(I would think that for 'well' behaved tables with an ascending identity would be catered for with the automatic statistics, ok I am spoiled a bit with SQL-server. 😀 )
Ben
June 9, 2017 at 6:47 am
GilaMonster - Friday, June 9, 2017 5:54 AMPerry Whittle - Friday, June 9, 2017 4:36 AMGilaMonster - Friday, June 9, 2017 3:55 AMStats that are a few weeks old on a table that's constantly getting data modifications is not ok, and may result in really bad executoin plans and resulting poor performance,Indeed, sometimes even a few days old
I've heard of an extreme case where stats updates were needed every 4 hours on a particular table. Not, fortunately, something I've encountered myself.
We had a VERY badly designed database where we were updating the statistics on one table every five minutes for about six months until we got the redesign completed (work involved, about a week, the rest of the time was arguing with the business to let us do the work). It was a band-aid across a severed artery, but it held things in place well enough, long enough, for us to finally get the fix done.
However, to the OP, the issue is how frequently the automatic updates occur and the fact that automatic updates are sampled as opposed to a full scan. The statistics are only updated automatically, by default, after 20% of the data has been changed (that's in a table with more than 1,000 rows). You can turn on traceflag 2371 to get better behavior out of automatic update with it performing more frequently as the number of rows increases. However, you may find certain tables, indexes or individual statistics that need to have a full scan run in order to get the most accuracy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2017 at 7:10 am
ben.brugman - Friday, June 9, 2017 6:46 AMStill suprised that SQL-server 'fails' at something so basic. 😉
Basic? What it should do? What's the correct behaviour that works for ALL possible situations that SQL server is used in?
Keep in mind, full scan stats updates read the entire table, potentially displacing portions of the buffer pool and all stats updates invalidate execution plans. resulting in increased CPU usage to regenerate those plans
btw, the ascending key problem is MUCH less of a problem with the new cardinality estimator, so if you're using compat mode for 2014 or 2016, you shouldn't be seeing the very severe problems with ascending keys that were possible on older versions.
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
June 9, 2017 at 7:49 am
Grant Fritchey - Friday, June 9, 2017 6:47 AMWe had a VERY badly designed database where we were updating the statistics on one table every five minutes for about six months until we got the redesign completed
:w00t:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply