SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Statistics - 3 / Latest Posts

RE: Statistics - 3

jfgoude — Wed, 09 Jan 2013 01:40:29 GMT

learn somethinggood answer by pure chance !

RE: Statistics - 3

Britt Cluff — Wed, 24 Oct 2012 20:25:57 GMT

Good question. Thanks for submitting.

RE: Statistics - 3

Miles Neale — Wed, 10 Oct 2012 15:51:24 GMT

I answered with hope there was such a thing and won. Excellent!

RE: Statistics - 3

SQLRNNR — Tue, 09 Oct 2012 12:38:44 GMT

[quote][b]saurabh.x.sinha (10/9/2012)[/b][hr][u][b]Summary !![/b][/u]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[/quote]FWIW - In SQL 2008 and up it uses colmodctr to track those changes.

RE: Statistics - 3

asifkareem — Tue, 09 Oct 2012 10:46:23 GMT

Thanks For good Question, its allow reading to different Article before you are going to attempt. but at the end +1

RE: Statistics - 3

LadyRuna — Tue, 09 Oct 2012 09:03:25 GMT

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....

RE: Statistics - 3

saurabh.x.sinha — Tue, 09 Oct 2012 08:59:28 GMT

[u][b]Summary !![/b][/u]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

RE: Statistics - 3

KWymore — Tue, 09 Oct 2012 08:40:31 GMT

Thanks for the question!

RE: Statistics - 3

arthurolcot — Tue, 09 Oct 2012 07:23:22 GMT

Nice, learnt something new today. Thanks.

RE: Statistics - 3

sestell1 — Tue, 09 Oct 2012 07:21:19 GMT

Very interesting, thanks for the question!

RE: Statistics - 3

(Bob Brown) — Tue, 09 Oct 2012 07:05:48 GMT

Thanks.

RE: Statistics - 3

Erav — Tue, 09 Oct 2012 07:03:49 GMT

A very good question. I learn something new..Thanks Ron!!:-)

RE: Statistics - 3

sql 15918 — Tue, 09 Oct 2012 06:45:14 GMT

Hmm... I sort of still think I´m right on this one (and that the answer is no). "Is there a method / setting that will alter the fixed rate of change threshold" I assumed to be no since there is no way to alter the fixed rate. You can however set sql server to instead use a dynamic rate but you can´t change it to another fixed rate such as 40%... Or have I misunderstood something?Cheers!

RE: Statistics - 3

Thomas Abraham — Tue, 09 Oct 2012 05:25:03 GMT

[quote][b]Koen Verbeeck (10/9/2012)[/b][hr]Thanks for another great question Ron.I choose Yes as I thought it would be sad if there wasn't such an option :-D[/quote]Same here. I thought that it wouldn't be worth posting if there wasn't a solution. Thanks for the question Ron.

RE: Statistics - 3

Divine Flame — Tue, 09 Oct 2012 04:01:46 GMT

Very informative Ron! Thanks :-)

RE: Statistics - 3

Stewart "Arturius" Campbell — Tue, 09 Oct 2012 03:58:46 GMT

That is very interesting.This question really got the grey matter working, thanks, Ron

RE: Statistics - 3

Gazareth — Tue, 09 Oct 2012 02:51:44 GMT

Thanks, good question. Actually guessed at the answer (hoped the specific versions mentioned was a clue), but it's taught me something I didn't know!It is interesting that it's been implemented as a trace flag rather than say through sp_configure.Maybe it will make it there in a future version or SP.Definitely a setting you only want to change if you know what you're doing!

RE: Statistics - 3

t.brown 89142 — Tue, 09 Oct 2012 02:00:44 GMT

After extensive search of MSDN and BOL, I (incorrectly) came to the conclusion that the only way to change the frequency was by scheduling a job to update stats.Some obscure traceflag does not strike me as a bone fide way of acheiving this. Hurrumph! ;-)Aren't traceflags things left behind by the Microsoft developers for their own purposes, and shouldn't be relied upon.

RE: Statistics - 3

Koen Verbeeck — Tue, 09 Oct 2012 00:18:32 GMT

Thanks for another great question Ron.I choose Yes as I thought it would be sad if there wasn't such an option :-D

RE: Statistics - 3

Lokesh Vij — Mon, 08 Oct 2012 22:52:02 GMT

Thanks Ron for this great question today as well. It required a little bit of reading before attempting it. Happy that I got it correct in the end :-)

Statistics - 3

bitbucket-25253 — Mon, 08 Oct 2012 20:46:48 GMT

Comments posted to this topic are about the item [B]Statistics - 3[/B]