April 2, 2012 at 7:16 am
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.
April 2, 2012 at 7:45 am
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.
April 2, 2012 at 7:54 am
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
April 2, 2012 at 9:09 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy