cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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

0 Kudos
10 Replies
Level 14

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

0 Kudos

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;

WHILE @@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;

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).

0 Kudos

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.

0 Kudos

What OS are you running DPA on? It's easier on linux as you just have two homes, but with windows, we create a service, so to do it on windows, there's a manual workaround that I can send you if you do want to go that route.

0 Kudos

We are running Windows. If you can send me the workaround that would be great! Thanks.

0 Kudos

Manual Install Steps:

=====================

1. Login to Windows with an Admin account.

2. Unzip the DPA installation zip file to the desired the Program Files directory

   on a local drive, for example:

      c:\program files

   Note: If Windows User Account Control is on, you may need to create a

         directory somewhere else, unzip to it, and then move the "SolarWinds"

         directory-tree under the "program files" directory.

   ** This document will now refer to x:\program files\SolarWinds\DPA as <install dir>.

                                                                           =============

3. (optional) If installing multiple instances of DPA on the same machine, change

   the service name and port to avoid conflicts.  To do so, follow these steps:

   a. Edit <install dir>\iwc\tomcat\bin\service.bat

      i.  Change ‘set SERVICE_NAME=IgnitePI’ to ‘IgnitePI_12’

      ii. Change ‘set PR_DISPLAYNAME=Ignite PI Server’ to ‘IgnitePI_12 Server’   

   b.  Edit <install dir>\iwc\tomcat\conf\server.xml

      i.  Change the server port from 8127 to 8137

      ii. Change the connector port from 8123 to 8133

       iii. Change the secure https port from 8124 to 8134

   c. Edit <install dir>\iwc\tomcat\licensing directory

      i. Rename it to licensing_<CurrentDate>

4. Open a command window. 

   Note: If installing on Windows with User Account Control, then you will need to open

         the window with Admin privileges by following these steps:

   a. In Windows Explorer, open the directory containing cmd.exe (usually c:\windows\system32)

   b. Right-click on cmd.exe and choose "Run as Administrator"

5. In the command window:

   a. Change directory to the <install dir> directory

   b. Create DPA as a windows service by typing the following command and pressing enter:

      createWindowsService.bat

      => You should see other windows open and close while the service is being created

6. Verify the existance of the DPA service:

   a. Open the Services window (usually under Control Panel > Administrative Tools)

   b. Look for a Service called "Ignite PI Server" and make sure it is Started.

      Note: If you changed the service name (e.g. to IgnitePI_12 Server), look for that

            name instead.

7. Use DPA:

   a. Open a browser (Internet Explorer or Mozilla Firefox)

   b. Type in the Ignite URL, for example:

      http://localhost:8123      <== if on the machine where Ignite is installed

      http://<servername>:8123   <== if on a different machine

0 Kudos

mandevil, how do I get access to the Beta? Do I need to do anything in specific?

Thanks

0 Kudos

bmrad​, aiglee.castillo​ - the PM has been notified here of your interest in the beta. Thanks!

0 Kudos

Awesome! Thanks! Now I just need to be authorized to download the beta

0 Kudos