April 5, 2011 at 6:42 am
This works fine for all our tables & indexes apart from one, where it always reports 0 user_updates and the all last_user_seek, last_user_scan and last_user_lookups are from over a month ago. This is a reasonable large table (400 million rows, 55gb) with one index (the clustering one) and very high traffic on writes, less so on reads. We're running a SQL Server 2008 mirrored pair.
Are there any known issues with dm_db_index_usage_stats or is there something else I need to check?
April 6, 2011 at 2:11 am
No thoughts on this one? 🙁
April 6, 2011 at 2:48 am
Please give more clarity of your question.
Thanks
April 6, 2011 at 3:17 am
I don't know how I could have been any clearer?
April 6, 2011 at 3:29 am
Is it all indexes on a certain table? Just one index on the table?
Are you 100% sure the table's in use, not maybe a copy of the table in a different schema?
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
April 6, 2011 at 3:34 am
One table and that table only has one index which is the clustering index. And yes 100% sure that table is in use! The rows in it are timestamped and you can see new rows flooding in to that table at a rapid rate during busy periods.
April 6, 2011 at 3:37 am
Inserts won't change the last seek, scan or lookup date. Only something that reads the table (select, update, delete) will change those.
If you think it's a bug, file it on Connect
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
April 6, 2011 at 3:41 am
According to dm_db_index_usage_stats the clustered index (the only index) has never been updated even though rows are constantly being added to it. Profiling reveals the table is being read from a fair bit and yet the last seek/scan/lookups are all from months ago. When I query the table directly in management studio the last seek/scan/lookups are never updated either. It's really weird.
April 6, 2011 at 3:46 am
Can you update the stats for the table and then run the index_usage?
----------
Ashish
April 6, 2011 at 3:47 am
I know this sounds silly, but can you run the following?
SELECT schema_name(schema_id), name as TableName, object_id FROM sys.objects where name = <name of the table in question>
Is there only one of them?
I've seen before a copy of a table gets created in another schema and people drive themselves crazy looking at one table when they think they're looking at a different one.
What query are you using to view the index usage stats?
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
April 6, 2011 at 3:48 am
crazy4sql (4/6/2011)
Can you update the stats for the table and then run the index_usage?
Why?
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
April 6, 2011 at 3:55 am
because I beleive the table and its stats are out dated.
What does the index fregmentation reports for the perticular index of the table?
----------
Ashish
April 6, 2011 at 3:57 am
crazy4sql (4/6/2011)
because I beleive the table and its stats are out dated.
Maybe, maybe not, but that has nothing to do with and no effect whatsoever on the index usage DMV. Neither does fragmentation.
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
April 6, 2011 at 4:49 am
Thanks Gila... although you did not solve the problem directly, you pointed me down the path to finding the issue and now I feel a little stupid!
sys.dm_db_index_usage_stats is of course not database-scoped... it is entire server scoped. Doh! So my wrapping sproc initially looked like this:
ALTER PROCEDURE [dbo].[dm_IndexUsage]
@Table NVARCHAR(255)
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @DB_ID SMALLINT ;
DECLARE @Table_ID INT ;
SET @DB_ID = DB_ID() ;
SET @Table_ID = OBJECT_ID(@TABLE) ;
IF @Table_ID IS NULL
BEGIN
PRINT N'Invalid Table' ;
END
ELSE
BEGIN
SELECT IX.name AS [Name],
SUBSTRING(IX.type_desc,0,5) AS [Type],
Usage.user_seeks AS [Seeks],
Usage.user_scans AS [Scans],
Usage.user_lookups AS [Lookups],
Usage.user_updates AS [Updates],
CAST((SELECT MAX(LastHit)
FROM (SELECT last_user_seek AS LastHit
UNION ALL
SELECT last_user_scan
UNION ALL
SELECT last_user_lookup) AS Dates) AS DATETIME2(0)) AS [Last Usage]
FROM sys.indexes IX
CROSS APPLY
(SELECT TOP 1 *
FROM sys.dm_db_index_usage_stats Usage
WHERE Usage.object_id = IX.object_id
AND Usage.index_id = IX.index_id) Usage
WHERE IX.OBJECT_ID = @Table_ID
ORDER BY
IX.type_desc,
IX.name
END
END
and of course the cross apply condition is missing a database_id clause. It just so happened that in another database (MSDB) there was a table with the exact same object_id as the table I was looking at in my database and it was getting the stats from that one instead. double doh! So adding the clause fixed this.
AND Usage.database_id = DB_ID()
Thanks!
April 6, 2011 at 5:13 am
Alex Webber (4/6/2011)
Thanks Gila... although you did not solve the problem directly, you pointed me down the path to finding the issue and now I feel a little stupid!
Don't worry, we've all done that or similar once or twice (or several times)
It just so happened that in another database (MSDB) there was a table with the exact same object_id as the table I was looking at in my database and it was getting the stats from that one instead. double doh!
Glad it's sorted out.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply