Auto Create Statistics

Question

Post reply

Auto Create Statistics

Mike Hays

SSCommitted

Points: 1871
More actions
January 6, 2014 at 9:58 pm

#302407

Comments posted to this topic are about the item Auto Create Statistics
January 7, 2014 at 1:30 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Nice back to basics question - thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Nice question, had to do some research.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Richard Warr SSCertifiable Points: 6966 More actions · Answer 3

Koen Verbeeck (1/7/2014)
Nice question, had to do some research.

I Googled it too 🙂

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

Thanks for the question!

Under what circumstances would you want is_auto_create_stats_on set to FALSE?

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Mike Hays SSCommitted Points: 1871 More actions · Answer 5

Thomas Abraham (1/7/2014)
Thanks for the question!
Under what circumstances would you want is_auto_create_stats_on set to FALSE?

Thomas asked a good question, and it took me a couple of minutes to find a good answer.

Chun Liu wrote a good blog regarding SharePoint:

http://blogs.msdn.com/b/chunliu/archive/2011/11/17/auto-update-statistics-and-auto-create-statistics-on-or-off-for-sharepoint-2010-databases.aspx

My simple guess is this setting would be turned off when you do not want the database overhead of statistics collected. This would probably be a very rare case.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

Mike Hays (1/7/2014)

My simple guess is this setting would be turned off when you do not want the database overhead of statistics collected. This would probably be a very rare case.

Thanks for the response. I figured that the reason would be performance due to overhead. But, couldn't think of an example in my experience where the overhead wasn't justified by performance improvement of later queries.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

January 7, 2014 at 9:06 am

#1678120

Nice: sent me to BOL... Thanks, Mike!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

nice question - thanks

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 9

Koen Verbeeck (1/7/2014)
Nice question, had to do some research.

+1 Did some reading before answering this question. Thanks Mike 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

sqlnaive SSCoach Points: 17437 More actions · Answer 10

"Been there done that". Had spent some time on statistics related issue so never had to think twice. 🙂

SQLWinther SSCertifiable Points: 6294 More actions · Answer 11

Easy question, but important to remeber. You may need to create multicolumn statistics manually sometimes.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform