How to identify your top ten table “hotspots” – SQL Server

hotspotsHave you ever been asked to identify the most accessed and utilized tables in your database and wondered if there was a quick way to do it?

This script which we have adapted from this article will give you the top ten on the database you select. It uses index stats to gather the information – this seems to give a pretty good indication.

Also remember as it is using system DMV’s the data will reset itself everytime the server is restarted.

--SQL Script begin
IF OBJECT_ID('tempdb..#HotSpots') IS NOT NULL
DROP TABLE #HotSpots
GO

CREATE TABLE #HotSpots
(Table_Name NVARCHAR(255), Seeks DEC, Scans DEC, Updates DEC)
INSERT INTO #HotSpots
SELECT DB_NAME() + '.' + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates 
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
WHERE b.object_id > 100 


SELECT Top 10 Table_Name as 'Table Name', sum(Seeks + Scans + Updates) as 'Total Accesses',
sum(Updates) as 'Total Writes', 
CONVERT(DEC(25,2),(sum(Updates)/sum(Seeks + Scans + Updates)*100)) as '% Accesses are Writes',
sum(Seeks + Scans) as 'Total Reads', 
CONVERT(DEC(25,2),(sum(Seeks + Scans)/sum(Seeks + Scans + Updates)*100)) as '% Accesses are Reads',
SUM(Seeks) as 'Read Seeks', CONVERT(DEC(25,2),(SUM(Seeks)/sum(Seeks + Scans)*100)) as '% Reads are Index Seeks', 
SUM(Scans) as 'Read Scans', CONVERT(DEC(25,2),(SUM(Scans)/sum(Seeks + Scans)*100)) as '% Reads are Index Scans'
FROM #HotSpots
GROUP by Table_Name
HAVING sum(Seeks + Scans) > 0
ORDER by sum(Seeks + Scans + Updates) DESC
DROP table #HotSpots
--SQL Script end
Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.