Thursday, October 13, 2011

SQL Server Most Utilized Tables

Ever wondered which tables in your databases are being used most heavily?  Below is a script for SQL Server 2005/2008 that will tell you exactly that - which tables are utilized most and what percentage of that use is reads and what percentage is writes.  Heavily utilized tables are prime candidates for SQL Server's Table Partitioning or moving to another filegroup (not necessarily recommended with EnterpriseOne) and besides, it's just gosh-darn fun knowing what's going on inside your databases.

Big Caveat - 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.


--SQL Script begin
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(''?'') > 4
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 AND a.user_seeks + a.user_scans + a.user_updates > 0
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)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Seeks',
SUM(UserScans) as 'Read Scans',
CONVERT(DEC(25,2),(SUM(UserScans)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Scans'
FROM #Temp
GROUP by TableName
ORDER BY 'Total Accesses' DESC
DROP table #Temp
--SQL Script end


The returned results will look like this:

Table NameTotal AccessesTotal Writes% Accesses are WritesTotal Reads% Accesses are ReadsRead Seeks% Reads are Index SeeksRead Scans% Reads are Index Scans
JDE_PRODUCTION.F061161196887461158860681.33118100139398.671181001096100.002970.00
JDE_PRODUCTION.F06189346335449957480.1193363779699.89933637770100.00260.00
JDE_PRODUCTION.F480190269270446533331251.5543735939248.4541843376095.67189256324.33
JDE_PRODUCTION.F0911739173150494891256.7068968402593.3068928392599.944001000.06


From this we can determine what tables are being utilized heavily and in what manner.   The last four columns deal with methods of index read access and are a bit beyond the scope of this article but if you really want to discuss that just ask in the comments and I'll be glad to expand.


Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

13 comments:

Himanshu said...

Hi Jeff,
This is Himanshu. Really nice article and helpful too. Could you tell me can i use same script if i am using DB2 or Oracle database with some format changes?
Regards,
Himanshu

Jeff Stevenson said...

You cannot use this for non-SQL Server databases, even with slight modifications. The basis for the script is the sys.dm_db_index_usage_stats Dynamic Management View, which is particular to SQL Server.

JB said...

Hi JD,

I have a question, in your post you have mentioned that the total read is sum(UserSeeks + UserScans).

In the sys.dm_db_index_usage_stats dmv there is another column user_lookups . Is this column not used for the calculation of total reads?

Thanks
JB

Jeff Stevenson said...

JB,

We use the sum of UserSeeks and UserScans because user_lookups only returns results for clustered indexes only. Seeks on non-clustered indexes reference the base row and this seek is what shows up in user_lookups.

User_lookups is not included in totals since some of the lookups included in it are already included in UserSeeks.

JB said...

I got side tracked and i almost forgot that i posted a question. Thanks for the response JD.

JB said...

Thanks for clearing my doubt

Alberto Dangelo said...

Hi Jeff,

I wonder if you can briefly explain how do you use the "%reads are index seeks" and "%reads are index scans" columns. Do you use them for performance analysis?

Thanks

Alberto

jxs2151 said...

Index Scans touch every row in a table while Index Seeks only hits rows that qualify. The query optimizer tries to find an index that it can utilize and if it does the result will be an index seek. If it cannot find a suitable index the result will be an index scan.

The scan will simply walk through the leaf pages and, as a result, is not very efficient. The seek will utilize the the B-tree structure in a more efficient path to the leaf page containing the data.

You can indeed use these figures to analyze index performance. If you are seeing a highly utilized table with the majority of accesses being scans it may be time to add an index or two.

A B-tree diagram:
http://goo.gl/7w4Mq

Bryan Homrighous said...

Thanks for the information. Very helpful.

sqllove said...

thanks, great info, here's another piece of code that i like to use in order to find a list of the most populated tables within my database : http://www.sqlpentruincepatori.ro/most-populated-tables-from-the-database-sql-server/

Srini said...

Great post...Thank you.

Zh zh said...

hi
i use this select but i have a problem.
how can i save this information before server reset or is shutdown.
because information in sys.dm_db_index_usage_stats be deleted in server rest.
please help me.
thank you

Anonymous said...

Thank you very much. This is very very helpful script. You save my day.