Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

How to show the busiest tables in the DB?

Hi,

I'm looking for DPA to tell me the busiest tables in my DB. I know I can do it with the query below, but I am not sure how to integrate that with DPA. I don't want to set up an alert as I don't have a preferred minimum or maximum, I just need information about usage.

SELECT

DB_NAME(ius.database_id)+'.'+t.NAME AS TableName,

SUM(ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) AS NbrTimesAccessed

FROM

sys.dm_db_index_usage_stats AS ius

INNER JOIN sys.tables AS t ON t.OBJECT_ID = ius.object_id

GROUP BY

database_id,

t.name

ORDER BY

NbrTimesAccessed DESC;

Any ideas of how we can integrate that info?

Thank you

Find more posts tagged with

Accepted answers

All comments

mandevil

How are you going to aggregate by table if there are multiple indexes?

This might be a bit of a kluge, but you could create a table in the monitored instance to store the data you pull (just insert the selected values along with a date/timestamp of when it was pulled. You can set a threshold to something ridiculously high that it will never get tripped or just send the notifications to a bogus email.

Just out of curiosity, are you looking for like an object heat map or something? You are just looking at the number of operations, but not the number of logical reads. In other words, you could have 1,000 user scans of a table that fits in one page, yet another object that results in 1,000,000 logical reads from just one scan. Which would you care more about?

We are working on what we call an index analysis, but it's really a lot more than that. Check out the post here.

I believe it would be extremely beneficial to you if I am correct about what intelligence you are looking to get from this.

I can't say any more at this time, but let me know if you'd like to participate in our beta (since you are a current customer) as we're always looking for feedback...

aiglee.castillo

The query already groups by indexes so the number returned is the number of accesses (both reads and inserts). I like your idea and would be good if we are looking to keep historical data. For now I implemented it the dirty way and did do an information alert that will not get triggered (or email me every week with the info) and will send me the info for all tables for all DBs I care about in the DB instance. I will post it below for anyone that might be interested.

As for what we are looking for, one of our senior developers wanted to know which tables are the most used ones in terms of access. Having said that, having a heat map like the one that you describe would also be awesome!

I just voted up for the index analysis. I actually have a weekly alert that reports me on inefficient statistics, and my most important point there was to figure out unused indexes, so the index analysis being worked on would be great!

I would participate in the beta! We have a few DPA installations and one of them is monitoring development servers. Trying the beta there would be a really good fit.

Thanks!

DECLARE @db_id INT;
DECLARE @db_name VARCHAR(50);
DECLARE c_db_ids CURSOR
FOR SELECT
database_id,
name
FROM
sys.databases
WHERE
name LIKE 'name%';

OPEN c_db_ids;

CREATE TABLE #report
(Table_Name VARCHAR(100),
Accesses INT);

FETCH c_db_ids INTO @db_id,
@db_name;

@Fetch_Status = 0
BEGIN
EXEC ('
    INSERT INTO #report
SELECT
DB_NAME(ius.database_id)+''.''+t.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates) AS NbrTimesAccessed
FROM
'+@db_name+';.sys.dm_db_index_usage_stats AS ius
INNER JOIN '+@db_name+';.sys.tables AS t ON t.OBJECT_ID = ius.object_id
WHERE ius.database_id = '+@db_id+';
GROUP BY
database_id,
t.name;
  ');
FETCH c_db_ids INTO @db_id,
@db_name;
END;

CLOSE c_db_ids;
DEALLOCATE c_db_ids;

SELECT * FROM #report ORDER BY Accesses DESC;
DROP TABLE #report;

mandevil

Thank you for the post! That's awesome!

Here is the beta site for DPA: Database Performance Analyzer Beta

I'll also let the PM know of your interest - remember, beta should be considered throw away once you are done with it, so you might want to stand up another DPA environment and double monitor dev for a bit (with such low overhead, the impact is negligible, especially in dev).

aiglee.castillo

Can't access the Beta, it says unauthorized

Let me check where we could do a second installation then, as I imagine having both DPA installations in the same server would not be possible/easy.