Script to identify heavily used tables and indexes in your database

Hi SQL Server Folks – There are many situation comes when we have a requirement in SQL Server to identify heavily used indexes in a database OR heavily used tables in a database. Below are the queries that can help you to find these things in SQL 2005\2008\R2.

Query to find heavily used indexes in your database :

Use database_name
go

SELECT CAST(OBJECT_NAME(usg.OBJECT_ID) AS VARCHAR(30)) AS ‘Table’,
CAST(idx.name AS VARCHAR(30)) AS ‘Index’,
usg.user_seeks + usg.user_scans + usg.user_lookups AS ‘Reads’,
usg.user_updates AS ‘Writes’
FROM sys.dm_db_index_usage_stats AS usg
INNER join sys.indexes AS idx
ON usg.OBJECT_ID = idx.OBJECT_ID
and idx.index_id = usg.index_id
WHERE OBJECTPROPERTY(usg.OBJECT_ID,’isusertable’) = 1
ORDER BY ‘Table’;
go

Query to find heavily used tables in your database :

Use database_name
go

IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(TableName NVARCHAR(255), UserSeeks DEC, UserScans DEC, UserUpdates DEC)
INSERT INTO #Temp
EXEC sp_MSForEachDB ‘USE [?]; IF DB_ID(”?”) = 6
BEGIN
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
END’

SELECT TableName as ‘Table Name’, sum(UserSeeks + UserScans + UserUpdates) as ‘Total Accesses’,
sum(UserUpdates) as ‘Total Writes’,
CONVERT(DEC(25,2),(sum(UserUpdates)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Writes’,
sum(UserSeeks + UserScans) as ‘Total Reads’,
CONVERT(DEC(25,2),(sum(UserSeeks + UserScans)/sum(UserSeeks + UserScans + UserUpdates)*100)) as ‘% Accesses are Reads’,
SUM(UserSeeks) as ‘Read Seeks’, CONVERT(DEC(25,2),(SUM(UserSeeks)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Seeks’,
SUM(UserScans) as ‘Read Scans’, CONVERT(DEC(25,2),(SUM(UserScans)/sum(UserSeeks + UserScans)*100)) as ‘% Reads are Index Scans’
FROM #Temp
GROUP by TableName
HAVING sum(UserSeeks + UserScans) > 0
ORDER by sum(UserSeeks + UserScans + UserUpdates) DESC
DROP table #Temp

Note :

This script makes use of the sys.dm_db_index_usage_stats SQL Dynamic Management View. The values returned by this DMV do not persist beyond server restarts. This means the information it gathers is only valid since the last SQL Server restart or (less likely) database attach. So, if you just re-started your database server you are not going to get good numbers. Also, this information is kept in cache and is subject to memory pressure flushing, not a likely scenario but possible.

The script will gather index information from which one can infer table access information. It uses the sp_MSForEachDB stored procedure to run through all databases on the instance, places the information in a temporary table, sums the values for index reads and writes, does a bunch of fancy math, rolls it up to the table level and returns the results to show reads, writes, percent of each and the type of index read.

Hope you’ll find this as useful. Thanks.

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

%d bloggers like this: