How can I tell if a certain table is being hit?

Question

Post reply

How can I tell if a certain table is being hit?

plamannkathy

SSCrazy

Points: 2190
More actions
November 4, 2015 at 11:15 am

#322356

Hi
Does anyone have code that I can use to determine if a certain table has been accessed in the past?
Thanks
Kathy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes and no.

You can use the DMV sys.dm_db_index_usage_stats, however that only has data back to the last time SQL Server started

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 2

Also as Gail's answer eluded to, it only helps if those tables have an index. If you have any heaps you are out of luck.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

sys.dm_db_index_usage_stats also has information on heaps.

That's easy enough to confirm:

CREATE TABLE MyTemporaryHeap (id int)

--Let's perform a user update

INSERT INTO MyTemporaryHeap

SELECT 1

SELECT last_user_lookup, last_user_scan, last_user_seek, last_user_update

FROM sys.dm_db_index_usage_stats

WHERE database_id=DB_ID() AND object_id=OBJECT_ID('MyTemporaryHeap')

--Now a user scan

SELECT * FROM MyTemporaryHeap

SELECT last_user_lookup, last_user_scan, last_user_seek, last_user_update

FROM sys.dm_db_index_usage_stats

WHERE database_id=DB_ID() AND object_id=OBJECT_ID('MyTemporaryHeap')

DROP TABLE MyTemporaryHeap

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

yb751 (11/4/2015)
Also as Gail's answer eluded to, it only helps if those tables have an index. If you have any heaps you are out of luck.

No, index usage stats does contain information on heaps as well as indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 5

GilaMonster (11/4/2015)
yb751 (11/4/2015)
Also as Gail's answer eluded to, it only helps if those tables have an index. If you have any heaps you are out of luck.
No, index usage stats does contain information on heaps as well as indexes.

Wow...did not realize that. If you lookup the documentation there is no mention of heaps at all.

https://msdn.microsoft.com/en-us/library/ms188755(v=sql.110).aspx

Even the way its worded, you wouldn't think it would include heaps. That being said I have found other sources online that did mention it.

Gotta love this site...learn something new everyday.

SELECT quote FROM brain WHERE original = 1
0 rows returned

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 6

USE AdventureWorks;

GO

SET ANSI_WARNINGS OFF;

SET NOCOUNT ON;

GO

WITH agg AS

(

SELECT

last_user_seek,

last_user_scan,

last_user_lookup,

last_user_update

FROM

sys.dm_db_index_usage_stats

WHERE

database_id = DB_ID()

)

SELECT

last_read = MAX(last_read),

last_write = MAX(last_write)

FROM

(

SELECT last_user_seek, NULL FROM agg

UNION ALL

SELECT last_user_scan, NULL FROM agg

UNION ALL

SELECT last_user_lookup, NULL FROM agg

UNION ALL

SELECT NULL, last_user_update FROM agg

) AS x (last_read, last_write);

plamannkathy SSCrazy Points: 2190 More actions · Answer 7

Thanks for all your input. This site has been invaluable to me since I started as a DBA last year.

Kathy