SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Statistics - 3


Statistics - 3

Author
Message
(Bob Brown)
(Bob Brown)
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1063 Visits: 1145
Thanks.
sestell1
sestell1
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3543 Visits: 3510
Very interesting, thanks for the question!
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3612 Visits: 1779
Nice, learnt something new today. Thanks.
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6589 Visits: 2397
Thanks for the question!
saurabh.x.sinha
saurabh.x.sinha
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 212
Summary !!
By default, SQL Server updates index statistics automatically. Frequency of automatic updates depends on number of rows in the table. If you allow SQL Server to update statistics automatically it will use the following rules:

1. If a table has 6 or fewer rows, statistics will be updated after 6 changes
2. If a table has 500 or fewer rows, statistics will be updated after 500 changes
3. If a table has more than 500 rows, statistics will be updated after 20% of the total rows plus 500 rows are changed (INSERTED, UPDATED or DELETED)
4. SQL Server uses the rowmodctr column of the sysindexes table to determine the number of changes since the last update of statistics
LadyRuna
LadyRuna
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 337
Thanks for posting this! I'd been hoping that there was a secret way of forcing SQL to automatically update stats more frequently. Our usual solution has always been to create a SQL job that runs update stats frequently... but this trace flag may be just the trick. I'll try it out on a test server....
asifkareem
asifkareem
Right there with Babe
Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)Right there with Babe (721 reputation)

Group: General Forum Members
Points: 721 Visits: 151
Thanks For good Question, its allow reading to different Article before you are going to attempt. but at the end +1
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66843 Visits: 18570
saurabh.x.sinha (10/9/2012)

Summary !!
By default, SQL Server updates index statistics automatically. Frequency of automatic updates depends on number of rows in the table. If you allow SQL Server to update statistics automatically it will use the following rules:

1. If a table has 6 or fewer rows, statistics will be updated after 6 changes
2. If a table has 500 or fewer rows, statistics will be updated after 500 changes
3. If a table has more than 500 rows, statistics will be updated after 20% of the total rows plus 500 rows are changed (INSERTED, UPDATED or DELETED)
4. SQL Server uses the rowmodctr column of the sysindexes table to determine the number of changes since the last update of statistics


FWIW - In SQL 2008 and up it uses colmodctr to track those changes.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4236 Visits: 1695
I answered with hope there was such a thing and won. Excellent!

Not all gray hairs are Dinosaurs!
Britt Cluff
Britt Cluff
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2377 Visits: 253
Good question. Thanks for submitting.

http://brittcluff.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search