October 9, 2012 at 9:03 am
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....
October 9, 2012 at 10:46 am
Thanks For good Question, its allow reading to different Article before you are going to attempt. but at the end +1
October 9, 2012 at 12:38 pm
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[/url]
Learn Extended Events
October 10, 2012 at 3:51 pm
I answered with hope there was such a thing and won. Excellent!
Not all gray hairs are Dinosaurs!
October 24, 2012 at 8:25 pm
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
January 9, 2013 at 1:40 am
learn something
good answer by pure chance !
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply