July 30, 2008 at 8:50 am
sbatemen, is this what you are looking for?
SELECT
[schema name]= SCHEMA_NAME(o.schema_id)
,[object name]= OBJECT_NAME(s.object_id)
,[stats name]= s.name
,[auto created?]= CASE s.auto_created WHEN 1 THEN 'yes' ELSE 'no' END
,[no recompute?]= case s.no_recompute WHEN 1 THEN 'yes' ELSE 'no' END
,[last updated]= STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s JOIN sys.objects o ON s.object_id = o.object_id
if you run sp_autostats on a table with no arguments except the table name, it will give you a list of each index (stats) on that table, whether or not they have autostats, and the last time the stats were updated.
If you run stats_date with the object id, and index id, (which I have done for the [last updated] column in the query above) it will give you the last time statistics were updated on that index/column stats.
dbcc show_statistics also tells you the last time statistics were updated, as well as the sampling used, and the details of the statistics, but it is a little more difficult to include into the results. I'd suggest creating a VBS or Perl script or something similar if you wanted to use dbcc show_statistics to get that information.
July 30, 2008 at 9:15 am
I answered the last question without thinking of the context of the article. The query I gave is all about whether statistics are set to autoupdate, and last time they were updated. But the article is talking about updating statistics immediately after you rebuild an index, and that is the sequence and timing of your jobs/maintenance activities - not an index setting.
July 31, 2008 at 1:42 am
thnx for the script, I work for a v.large (global) plc. tradionally IT-NETWORK staff are DBA's, us Software SQL Developers have to beg.4.any.rights**
I have just proved that we are NOT updating statistics, rowcount[2735]!
** Not as bad as it sounds, as our vast Corporate Network is in a high state of flux, I won't want their asprin count.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply