Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
AUTO_CREATE_STATISTICS at table level

Answered AUTO_CREATE_STATISTICS at table level

  • Tuesday, October 09, 2012 6:45 AM
     
     
    Sign In to Vote
    0

    Hi All,

    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

     

All Replies

  • Tuesday, October 09, 2012 7:08 AM
     
     
    Sign In to Vote
    0

    Use "sp_autostats" to disable/enable the statistics for table/index.Please refer the below link:

    http://msdn.microsoft.com/en-us/library/ms188775.aspx

    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.


     
  • Tuesday, October 09, 2012 8:47 AM
     
     
    Sign In to Vote
    0

    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

    Blog , Personal website

    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
     
     
    Sign In to Vote
    0

    Related to the issue, STATISTICS should be updated nightly for best performance:

    http://www.sqlusa.com/articles/query-optimization/

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

     
  • Wednesday, October 10, 2012 7:22 AM
     
     Answered
    Sign In to Vote
    0

    Hi sqljoker,

    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 .


    Regards,
    Amy Peng

    Amy Peng

    TechNet Community Support

    • Marked As Answer by sqljoker Wednesday, October 10, 2012 2:29 PM
    •  
     
  • Wednesday, October 10, 2012 7:47 AM
     
     
    Sign In to Vote
    0

    Hi,

                   Refer below link for complete information about statisitcs and also for what you need

                     http://sathyadb.blogspot.in/2012/08/sql-server-statistics.html

    Thanks & Regards,

    Sathya

    http://sathyadb.blogspot.in/

    sathyas