Tuesday, October 09, 2012 6:45 AM
I am on SQL Server 2008.
We have AUTO_CREATE_STATISTICS set to TRUE at database level. I assume that is the default setting.
I do not want to "auto create stats" on some of the tables (not all). How can I do that?
Thanks in advance
Tuesday, October 09, 2012 7:08 AM
Use "sp_autostats" to disable/enable the statistics for table/index.Please refer the below link:
However, its not a good idea to disable this option as it might cause lots of performance issues in your system. Please evaluate carefully.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
- Edited by Latheesh NKMicrosoft Community Contributor Tuesday, October 09, 2012 7:09 AM
Tuesday, October 09, 2012 8:47 AM
while you can easily enable disable this for a table i would like to understand the reason why you want to disable this at first place?
If you dont want your users\queries to keep on waiting while sql is updating the stats you can enable async update stats option, this will allow sql to process the query this time without waiting for the latest stats but in the background start updating the stats --- this might help you.
Sarabpreet Singh Anand
This posting is provided , "AS IS" with no warranties, and confers no rights.
Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.
Tuesday, October 09, 2012 10:59 AM
Related to the issue, STATISTICS should be updated nightly for best performance:
Wednesday, October 10, 2012 7:22 AM
The query optimizer creates statistics in the following ways:
One way is when AUTO_CREATE_STATISTICS is on.
The second way is that the query optimizer creates statistics for indexes on tables or views when the index is created. These statistics are created on the key columns of the index. If the index is a filtered index, the query optimizer creates filtered statistics on the same subset of rows specified for the filtered index.
So you can set AUTO_CREATE_STATISTICS off, then use the second way to Improve Query Performance.
Please refer to:
Using Statistics to Improve Query Performance: http://msdn.microsoft.com/en-us/library/ms190397(v=sql.100).aspx .
TechNet Community Support
- Marked As Answer by sqljoker Wednesday, October 10, 2012 2:29 PM
Wednesday, October 10, 2012 7:47 AM
Refer below link for complete information about statisitcs and also for what you need
Thanks & Regards,