Remove From My Forums

Statistics

Question
0

Sign in to vote

As per my knowledge, sql server statistics get updated when a number of rows(which are required to met the threshold level to update statistics) get inserted into a table. But recently I check the statistics of some tables on production environment, in which thousands of records get inserted daily, are having outdated statistics.

I wonder why sql server did not trigger update statistics mechanism on those tables even after thousands of rows are getting inserted.

I also read in some blogs that sql server update rows by sample the rows . How this sampling take place and is updating statistics on the basis of samples is an effective way to get up to date statistics.

Please HELP.....

Saturday, December 12, 2015 9:22 AM

Answers

0

Sign in to vote
Here are articles about how the auto update statistics works:

https://support.microsoft.com/en-us/kb/195565

https://www.simple-talk.com/sql/t-sql-programming/13-things-you-should-know-about-statistics-and-the-query-optimizer/

https://msdn.microsoft.com/en-us/library/dd535534%28SQL.100%29.aspx?f=255&MSPPError=-2147217396

http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

Only if the auto stats is not working good enough for you, then add an update statistics to your maintenance plans. I would recommend the maintenance scripts by Ola Hallengren:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

the changed rows requirement for auto update stats increases as the table size increases, and it is updated when a query plan is compiled.

Christmas spirit all year long Please remember to vote on useful replies. Mark answers. Help to answer questions of others while you are visting the forums. Thank you.
- Edited by AbsolutelyFreeWeb Saturday, December 12, 2015 5:20 PM
- Proposed as answer by pituachMVP Tuesday, December 15, 2015 6:34 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, December 21, 2015 7:50 AM
Saturday, December 12, 2015 5:15 PM

All replies

0

Sign in to vote
Here are articles about how the auto update statistics works:

https://support.microsoft.com/en-us/kb/195565

https://www.simple-talk.com/sql/t-sql-programming/13-things-you-should-know-about-statistics-and-the-query-optimizer/

https://msdn.microsoft.com/en-us/library/dd535534%28SQL.100%29.aspx?f=255&MSPPError=-2147217396

http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

Only if the auto stats is not working good enough for you, then add an update statistics to your maintenance plans. I would recommend the maintenance scripts by Ola Hallengren:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

the changed rows requirement for auto update stats increases as the table size increases, and it is updated when a query plan is compiled.

Christmas spirit all year long Please remember to vote on useful replies. Mark answers. Help to answer questions of others while you are visting the forums. Thank you.
- Edited by AbsolutelyFreeWeb Saturday, December 12, 2015 5:20 PM
- Proposed as answer by pituachMVP Tuesday, December 15, 2015 6:34 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, December 21, 2015 7:50 AM
Saturday, December 12, 2015 5:15 PM
0

Sign in to vote

Hi bills1988,

what value do you have for the database-wide "Auto Update Statistics" setting? Is it True or False?

Definitely, you should set it to True unless you have very specific requirements. In most cases statistics that is based on a sample is good enough (it might be a case if you have an extremely unbalanced data distribution - in this case the UPDATE STATISTICS WITH FULLSCAN should help).

And yes, take a look at links AbsolutelyFreeWeb has provided in the previous post.

Regards,

S.

Monday, December 14, 2015 6:16 PM
0

Sign in to vote

Do you run DBCC SHOW_STATISTICS and it shows that statistics are not updated?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Tuesday, December 15, 2015 6:53 AM

Answerer

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Answered by:

Statistics

Question

Answers

All replies