Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
amirs
Constraint Violating Yak Guru
260 Posts |
 Posted - 2008-05-28 : 03:03:41
|
| hii have planning shedule of dbcc sp_createstats of my database in weekly.so how can help to performance of sql server |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-28 : 03:15:35
|
| Rather don't. Make sure that auto create stats is enabled (database setting) and let the query processor/optimiser decide what stats it needs to create in the database--Gail Shaw |
 |
|
|
amirs
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-05-28 : 03:44:01
|
| ok it is true but in my understand is not 100% true to enable of createstat and updatestats. so my planning to sheduling the excute procedure of sp_createstats and sp_updatestats so how effect of our database.I would like to get user experiences |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-28 : 07:37:22
|
| Sorry, I don't quite understand what you're asking--Gail Shaw |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-29 : 13:35:13
|
| You should not create a job to create statistics, but you should create one to manually update statistics. For a stored procedure to do this, check out my signature. There are times when you shouldn't have auto update stats turned on. Luckily in SQL Server 2005, we can now do it asynchronously, but I'm surprised that it is turned off by default.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
amirs
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-06-05 : 08:22:35
|
| Thanks for the answer . We have auto create and update stats on . Update stas are not asyncronous .As I understand despite having auto create / update stats on , there are times one need to run this manually. Correct me if wrong . Tara can u tell me briefly what's the difference between isp_updatestats (created by u ) and the one provided with SQL 2005 .What is the frequency one should run it . For sp_createstats (if it's to be run some times ) what should be the arguments to run the same . |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-05 : 13:02:37
|
| The difference between mine and theirs is that mine allows you to set the sampling ratio, while theirs only let's you use the original ratio. There are times when you can get away with a sampling ratio of 25% or less perhaps, other times you have want to do 100%.The frequency it should be run depends on your system. You should consider enabling asynchronous update stats. In my opinion, this should be enabled by default by Microsoft.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
|
|
sp_createstats