March 5, 2015 at 12:06 pm
When I run a query (Glenn Berry's) to check when statistics were updated, I find some rows with 'IndexName' which were updated more than year ago and have millions of rows.
Then I run sys.dm_db_index_usage_stats with sys.indexes and filter those outdated index but results come out nothing.
How do I query and find those indexes ever been used? And
How to I find those table where those indexes are, ever been updated, inserted or deleted?
Thanks
March 5, 2015 at 3:31 pm
Please send us the two queries that you are running.
Also, it may be a little bit of a language barrier, but I am having trouble understanding what you want to find. Could you try to explain that more fully?
March 6, 2015 at 7:23 am
Thanks David Moutray for your reply. I ran this script:
-- When were Statistics last updated on all indexes? (Query 51) (Statistics Update)
SELECT o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active
I found some rows with outdated statistics. Now what I am trying to find is, those indexes were ever used PLUS also trying to find out if the Tables (which hold those indexes) ever been Inserted, Updated or Deleted?
March 6, 2015 at 9:47 am
Do you run any job to refresh stats every week? How often do you defrag your Indexes?
If for some reason the answer is no for any of those, I think that you should start running those 1st, wait 1 or 2 weeks, before start removing Indexes or making any changes.
I fully recommend you Ola Hallengren's solution, which is 100% free.
March 6, 2015 at 12:49 pm
I only run 'rebuild' job> 30 percent with 'Limited' option. will you share Ola Hallengren script here? it's kinda hard for me to put together.
March 6, 2015 at 4:29 pm
Tac11 (3/6/2015)
I only run 'rebuild' job> 30 percent with 'Limited' option. will you share Ola Hallengren script here? it's kinda hard for me to put together.
Visit his site: https://ola.hallengren.com/sql-server-backup.html
Install it and open the jobs, but I am telling you, that logic is too complex to copy paste here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply