November 15, 2013 at 3:20 am
Hi All
I'm analyzing unused indexes via the standard report for indexes usage stats for two clients and I have the following dates:
Client1
Last User Seek Time Last User Scan TimeLast User Lookup Time Last User Update Time
1-1-1900 12:00:00 AM 1-1-1900 12:00:00 AM1-1-1900 12:00:00 AM 1-1-1900 12:00:00 AM
Last System Seek Time Last System Scan Time Last System Lookup Time Last System Update Time
1-1-1900 12:00:00 AM 9-20-2013 03:25:18 AM1-1-1900 12:00:00 AM 1-1-1900 12:00:00 AM
Client2
Last User Seek TimeLast User Scan TimeLast User Lookup TimeLast User Update Time
1-2-1900 12:00:00 AM1-2-1900 12:00:00 AM1-2-1900 12:00:00 AM1-2-1900 12:00:00 AM
Last System Seek TimeLast System Scan Time Last System Lookup TimeLast System Update Time
1-2-1900 12:00:00 AM11-13-2013 8:53:29 PM 1-2-1900 12:00:00 AM 1-2-1900 12:00:00 AM
Seeing the same for a third client, i saw dates 1-1-1900, but not 1-2-1900.
Can someone tell why for the second client the date is 1-2-1900 ?
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 15, 2013 at 3:34 am
You'd need to look at the query that the report is running. The DMV shows null if an index hasn't been used, so the dates you see come from the query that's been run (or the report formatting), not from the DMV itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2013 at 4:18 am
The select I caught via the profiler for Indexes usage statistcs is the following:
Select
(dense_rank() over (order by s.name,t.name))%2 as l1
, (dense_rank() over (order by s.name,t.name,i.name))%2 as l2
,s.name as [schema_name]
, t.name as [table_name]
, i.name as [index_name]
, i.type_desc
, case when iu.object_id is NULL then '''' else convert(varchar(20),iu.object_id) end as object_id
, case when iu.index_id is NULL then '''' else convert(varchar(20),iu.index_id) end as index_id
, case when iu.user_seeks is NULL then '''' else convert(varchar(20),iu.user_seeks) end as seek_user
, case when iu.user_scans is NULL then '''' else convert(varchar(20),iu.user_scans) end as scan_user
, case when iu.user_updates is NULL then '''' else convert(varchar(20),iu.user_updates) end as update_user
, case when iu.last_user_seek is NULL then '''' else iu.last_user_seek end as time_seek_user
, case when iu.last_user_scan is NULL then '''' else iu.last_user_scan end as time_scan_user
, case when iu.last_user_lookup is NULL then '''' else iu.last_user_lookup end as time_lookup_user
, case when iu.last_user_update is NULL then '''' else iu.last_user_update end as time_update_user
, case when iu.system_seeks is NULL then '''' else convert(varchar(20),iu.system_seeks) end as seek_system
, case when iu.system_scans is NULL then '''' else convert(varchar(20),iu.system_scans) end scan_system
, case when iu.system_updates is NULL then '''' else convert(varchar(20),iu.system_updates) end as update_system
, case when iu.last_system_seek is NULL then '''' else iu.last_system_seek end as time_seek_system
, case when iu.last_system_scan is NULL then '''' else iu.last_system_scan end as time_scan_system
, case when iu.last_system_lookup is NULL then '''' else iu.last_system_lookup end as time_lookup_system
, case when iu.last_system_update is NULL then '''' else iu.last_system_update end as time_update_system
from sys.dm_db_index_usage_stats iu
inner join sys.indexes i on ((iu.index_id = i.index_id) and (iu.object_id = i.object_id))
inner join sys.tables t on ( i.object_id = t.object_id )
inner join sys.schemas s on (s.schema_id = t.schema_id)
where iu.database_id = db_id() and i.type <> 0
order by s.name,t.name,i.name
It seems is the report formatting. Thank you Gail!
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply