Database Reads/Writes Hot spots

Found following script useful to get database reads/writes hot spots. Must care that these cumulative statistics are from instance start date.

Reference: http://www.dbafire.com/2014/05/26/identify-top-ten-table-hotspots-sql-server/

 
--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 100 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s