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

• Minto Minto(quendans)

  SSCrazy

  Points: 2533

  More actions

  April 2, 2012 at 7:16 am

  #150882

  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.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  April 2, 2012 at 7:45 am

  #1467625

  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]

• Ant-Green

  SSC Guru

  Points: 113543

  More actions

  April 2, 2012 at 7:54 am

  #1467630

  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

• Grant Fritchey

  SSC Guru

  Points: 399061

  More actions

  April 2, 2012 at 9:09 am

  #1467675

  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