| Author |
Topic |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2008-11-13 : 05:56:37
|
| Dear All,i've read in an article that auto create statistics will slower the queries. if so, what is the use of auto create statistics?in my environment, production is using for only data taking, replication server is using for searching and for reports.now i'm thinking of auto create statistics to disable on replication server. because i'm querying from there only. is it a good idea? please correct me if i'm wrong....ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:03:03
|
| auto update stats was considere a relatively bad thing before sql 2005. 2005 version now has async auto update stats which works well.you can't go wrong with a nightly scheduled job where you update stats by yourself._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-11-13 : 06:07:20
|
| i'm not updating statistics with any jobs. in my servers, for all the databases, autocreate statistics is true. is it ok? or i need to set that as false?i'm using sql server 2005 ent edition on windows server 2003.ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-13 : 06:11:02
|
| what exactly did you not understand from my answer?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-13 : 07:08:32
|
| SQL Server 2005 introduces additional information that is collected by statistics createdon char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns.This additional information, called a string summary, helps the query optimizerestimate the selectivity of query predicates on string patterns, which leads to betterestimates of result set sizes when a query uses LIKE conditions. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-11-13 : 07:14:12
|
| Leave the autoupdate on unless you know that it's causing a problem. If you know that it's causing a problem, and you're willing to risk poor execution plans due to out of date stats, then switch it off and update the stats manually on a regular basis.[url]http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/[/url]--Gail ShawSQL Server MVP |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-13 : 10:28:19
|
| We leave it on in our production environment. |
|
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2008-11-19 : 04:33:36
|
| actually i've taken a trace using sql profiler. i've given that trace as input to the database tuning advisor. the advisor generated some scripts for me and estimated performance gain says that 59%. in those script files, i've seen so many create statistics statements and non clustered indexes. non clustered indexes i've created already, but what about this create statistics? already autocreate statistics is true. again it came like this....will it take some time? i need to create at off peak times?ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
|
auto create statistics is good or bad