June 3, 2015 at 3:11 pm
I have been doing some research on sp_UpdateStats stored procedure, UPDATE STATISTICS SQL Statement, and Auto Update Statistics. Sounds like 'sp_UpdateStats' stored procedure updates the database statistics for all tables and indexes in the database). Sounds like the 'UPDATE STATISTICS' SQL Statement updates the statistics at the table or index level. And the 'Auto Update Statistics set to ON' database configuration updates all tables and indexes within the database.
With the 'Auto Update Statistics set to ON', I read that 'the query optimizer will continue to routinely update statistics.' But, how often does this configuration updates the statistics? Is it best to just update statistics on the tables manually (when needed) or do it weekly with a maintance job using the 'UPDATE STATISTICS' SQL Statement?
Thanks. I am still researching.
June 3, 2015 at 3:29 pm
HookSqlDba7 (6/3/2015)
I have been doing some research on sp_UpdateStats stored procedure, UPDATE STATISTICS SQL Statement, and Auto Update Statistics. Sounds like 'sp_UpdateStats' stored procedure updates the database statistics for all tables and indexes in the database). Sounds like the 'UPDATE STATISTICS' SQL Statement updates the statistics at the table or index level. And the 'Auto Update Statistics set to ON' database configuration updates all tables and indexes within the database.With the 'Auto Update Statistics set to ON', I read that 'the query optimizer will continue to routinely update statistics.' But, how often does this configuration updates the statistics? Is it best to just update statistics on the tables manually (when needed) or do it weekly with a maintance job using the 'UPDATE STATISTICS' SQL Statement?
Thanks. I am still researching.
You should go read this and then set it up for your maintenance. https://ola.hallengren.com/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2015 at 1:56 am
There is actually no better way of doing regular maintance on statistics !
Use the Maintance Solution of Ola Hallengren
June 4, 2015 at 2:55 am
HookSqlDba7 (6/3/2015)
But, how often does this configuration updates the statistics?
When the number of rows that have changed since the last stats update exceeds 20% of the table (by default, there are several ways to change that now)
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 4, 2015 at 4:00 am
GilaMonster (6/4/2015)
HookSqlDba7 (6/3/2015)
But, how often does this configuration updates the statistics?When the number of rows that have changed since the last stats update exceeds 20% of the table (by default, there are several ways to change that now)
Gail does that 20% rule only apply when n > 500? ( n refers to a table's cardinality )
June 4, 2015 at 4:20 am
BL0B_EATER (6/4/2015)
GilaMonster (6/4/2015)
HookSqlDba7 (6/3/2015)
But, how often does this configuration updates the statistics?When the number of rows that have changed since the last stats update exceeds 20% of the table (by default, there are several ways to change that now)
Gail does that 20% rule only apply when n > 500? ( n refers to a table's cardinality )
Yes.
Are you asking that because you're unsure, or to point out I'm wrong?
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 4, 2015 at 4:23 am
GilaMonster (6/4/2015)
BL0B_EATER (6/4/2015)
GilaMonster (6/4/2015)
HookSqlDba7 (6/3/2015)
But, how often does this configuration updates the statistics?When the number of rows that have changed since the last stats update exceeds 20% of the table (by default, there are several ways to change that now)
Gail does that 20% rule only apply when n > 500? ( n refers to a table's cardinality )
Yes.
Are you asking that because you're unsure, or to point out I'm wrong?
Unsure. As I got some issues in prod around this area hence need to look at your last line "there are several ways to change that now".
June 4, 2015 at 4:25 am
BL0B_EATER (6/4/2015)
GilaMonster (6/4/2015)
BL0B_EATER (6/4/2015)
GilaMonster (6/4/2015)
HookSqlDba7 (6/3/2015)
But, how often does this configuration updates the statistics?When the number of rows that have changed since the last stats update exceeds 20% of the table (by default, there are several ways to change that now)
Gail does that 20% rule only apply when n > 500? ( n refers to a table's cardinality )
Yes.
Are you asking that because you're unsure, or to point out I'm wrong?
Unsure. As I got some issues in prod around this area hence need to look at your last line "there are several ways to change that now".
Cool.
Sorry, I get those kind of 'passive-aggressive' questions a little too often and they're damn annoying.
The 'several ways' are mostly traceflag based. I can't recall exact numbers or effects off the top of my head.
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 4, 2015 at 7:22 am
To answer your question... I have AUTO STATS on, plus I generally update stats in a Maint plan job once a week. There are a few key tables/databases that I have found once in a while queries start running long and I manually ran the update stats job and things ran better. So, for the key ones like that I update stats after large amounts of data change. You need to know your systems and how much data change happens.
We have one table in one very active database that gets 1/8th of the data deleted and new rows inserted. I have a job run that updates stats on it daily after that process. More of a make sure stats are accurate as it is a highly visable/used table.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply