locked
use of UPDATE_STATISTICS RRS feed

  • Question

  • Could you please tell me what is  UPDATE_STATISTICS command and when should we  use it ?

    Monday, October 18, 2010 5:32 PM

Answers

  • SQL Server updates statistics automatically by default. Statistics update triggers by # of modifications on the table. By default statistics updates synchronously at the time of the query execution after threshold is met. That behavior is fine on most part of the cases. You can consider to update statistics manually if:

    1. You upgrade to the new version of SQL Server. You can use sp_updatestats stored procedure to update all statistics in the database in such case

    2. The data is updating rarely and based on batches and you don't want the queries to be slow down during statistics update phase after you process the new batch. Another option in such case is turning on asynchronous statistics update.

    I would recommend not to alter default SQL Server behavior unless you have specific reasons to do that.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 18, 2010 6:34 PM
  • Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

    Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

    Here is a nice article which clearly explain

    1. What are Query Optimization Statistics?

    2. Using the Database-Wide Statistics Options

    3. Determining When to Create Statistics

    4. Determining When to Update Statistics

    5. Designing Queries that Use Statistics Effectively

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

    Please go through this article and you will be able to know what/why statistics ...

    hope this will give you clear concepts on statistics....


    thanks

    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Tuesday, October 19, 2010 4:15 AM
  • Are you sure you meant the UPDATE_STATISTICS and not the UPDATE STATISTICS - BOL Reference
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 6:25 PM
    Answerer

All replies

  • Are you sure you meant the UPDATE_STATISTICS and not the UPDATE STATISTICS - BOL Reference
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 6:25 PM
    Answerer
  • SQL Server updates statistics automatically by default. Statistics update triggers by # of modifications on the table. By default statistics updates synchronously at the time of the query execution after threshold is met. That behavior is fine on most part of the cases. You can consider to update statistics manually if:

    1. You upgrade to the new version of SQL Server. You can use sp_updatestats stored procedure to update all statistics in the database in such case

    2. The data is updating rarely and based on batches and you don't want the queries to be slow down during statistics update phase after you process the new batch. Another option in such case is turning on asynchronous statistics update.

    I would recommend not to alter default SQL Server behavior unless you have specific reasons to do that.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, October 18, 2010 6:34 PM
  • Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

    Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.

    Here is a nice article which clearly explain

    1. What are Query Optimization Statistics?

    2. Using the Database-Wide Statistics Options

    3. Determining When to Create Statistics

    4. Determining When to Update Statistics

    5. Designing Queries that Use Statistics Effectively

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

    Please go through this article and you will be able to know what/why statistics ...

    hope this will give you clear concepts on statistics....


    thanks

    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Tuesday, October 19, 2010 4:15 AM