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.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: 1429 Visits: 1145
Thanks.
sestell1
sestell1
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6088 Visits: 3693
Very interesting, thanks for the question!
Arthur Olcot
Arthur Olcot
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4662 Visits: 1779
Nice, learnt something new today. Thanks.
Ken Wymore
Ken Wymore
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9666 Visits: 2470
Thanks for the question!
saurabh.x.sinha
saurabh.x.sinha
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 219
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 351
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
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 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 (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145917 Visits: 18652
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
Learn Extended Events

Miles Neale
Miles Neale
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

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

Not all gray hairs are Dinosaurs!
Britt Cluff
Britt Cluff
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3315 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