how to check Update Statistics is being executed automatically daily or not

  • Hi All,

    In our environment we have kept Database properties of a database ,Auto Update Statistics is set to TRUE.

    We want to check whether Update Statistics is being executed automatically daily or not.

    Please suggest me on this request.

    We are using sql server 2008 standard edittion.

    Thansk in advance.

  • Here is a code sample, that you might want to include in a job set to run on a daily basis. Insert the results into a table for review at you convinence,

    USE AdventureWorks;

    GO

    SELECT name AS index_name,

    STATS_DATE(object_id, index_id) AS statistics_update_date

    FROM sys.indexes

    WHERE object_id = OBJECT_ID('Person.Address');

    GO

    Of course change the database name and schema.table to suit your needs.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • from what I remember, if you dont have more than 5% data change in the table then stats dont get updated unless you manually issue update statistics.

    this is great for smaller tables, but when your looking at big tables you might be lucky to get 5% data change, so for a tables with 1million rows, you need 50,000 rows to be modified before stats are rebuilt which may be never

    adding on from Ron's code snippit, this will do the same but for all statistics, not just ones created with indexes

    SELECT name AS stats_name,

    STATS_DATE(object_id, stats_id) AS statistics_update_date

    FROM sys.stats

    GO

  • To check when the last time statistics were updated, use the STATS_DATE function.

    The rules for when auto update fires are a bit more complicated. They're listed here.

    If you add a row to an empty table that has statistics, they get updated.

    If you have fewer than 500 rows in a table and you have more than 500 rows changed, they get updated.

    If you have greater than 500 rows, then you have to have 500 rows + 20% change before they get updated.

    There are a few more details listed there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply