December 18, 2009 at 7:55 am
Dear Friends,
is there a way we can find out statistics information like (last updated, rows, sampled rows) for all indexes with their table names, instead of doing it individually by running:
dbcc show_statistics ('tablename','idxname')
Many thanks.
December 18, 2009 at 9:06 am
Excuse this reply as it is not a complete answer to your question, but hopefully it will give you a place to start.
For date of last update of statistics for a table look at
SQL Server 2005 Books Online (September 2007)
sp_autostats (Transact-SQL)
If you want to know who/what created the statistic look at:
sys.stats (Transact-SQL)
December 18, 2009 at 9:10 am
This won't completely answer your question either, but it'll provide quite a bit of information for you.
Use the the dynamic management views for indexes. I'd suggest starting with sys.dm_db_index_usage_stats. You can get a lot of data, all at once for all the indexes in the system, or for each database, a given table or view, or for an individual index.
"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
December 18, 2009 at 1:41 pm
Thank you guys for the information.
In sp_autostats again i have to specify table name to get the information. There are 100's of tables, so i'll have to run it individually for each table. is there a way i can use this stored procedure to get the result for all tables sorted by last updated date.
This only gives me the information of when the statistics were last updated. It doesn't give me any information about rows or sampled.
Basically,I want to update statistics with full scan of all the indexes where rows<>rows sampled. Instead of doing it individually, i wanted to do it for all the indexes which fullfilled the above criteria.
sys.dm_db_index_usage_stats does not give me the above information either. Is there any other DMV that i would need to combine to get information about indexes where rows<>rows sampled.
I am not sure if this can be done.
December 18, 2009 at 4:48 pm
Read this article, pay attention to the discussion of the undocumented stored procedure
SP_MSForEachTable
sp_msforeachtable is very similar to sp_msforeachdb, except executing the command against every table in the database. For instance, if there is a test database and all tables in the test database need to emptied while retaining the table structures, this command could be used:
at:
[http://www.sqlservercentral.com/articles/Stored+Procedures/62868//url]
Keep in mind this is undocumented and hence subject to change by MS, but it might assist you in your current endeavor. And by all means test, test, and test again before using this on a production DB.
December 28, 2009 at 9:50 pm
Use the following Query to find the statistics last updated date
select object_name (i.id)as objectname,i.name as indexname,i.origfillfactor,i.rowcnt,i.rowmodctr ,STATS_DATE(i.id, i.indid) as ix_Statistics_Date,o.instrig,o.updtrig,o.deltrig,o.seltrig
from sysindexes i INNER JOIN dbo.sysobjects o ON i.id = o.id
where rowcnt >1000 and i.name not like'sys%'and object_name(i.id)not like'sys%'
order by rowcnt desc
This is very useful
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply