Monitoring table-level activity in a SQL Server database by using T-SQL

Ryan McCauleySenior Data Architect
CERTIFIED EXPERT
I'm a lover of data - management, querying, and presentation. I'm happy to help make things reliable, quick, and arranged to tell a story.
Published:
Tracking database activity

There are a number of ways to view SQL Server activity; SQL Profiler is one of the most popular, which allows you to see the number of type of transactions being excuted. However, what if you want to see the level of activity on each table in your database? How many rows are being queried or changed during a period of time or every second? A tool like SQL Profiler will give you the type and volume of transactions in motion at any given time (and the T-SQL actually being executed), but you can't use it to directly tell which database tables are being touched and how many rows are affected.

Using sys.dm_db_index_usage_stats
 
However, as of SQL Server 2008, there's a handy dynamic management view called sys.dm_db_index_usage_stats that shows you number of rows in both SELECT and DML statements against all the tables and indexes in your database, either since the object was created or since the database instance was last restarted:
 
SELECT *
                        FROM sys.dm_db_index_usage_stats

Open in new window


However, it uses object and index ID numbers and isn't very friendly, as well as displaying usage numbers for system-based processes, which aren't of much use when you're comparing application activity. That said, here's a query for the same view, but using object/index names:
 

SELECT OBJECT_NAME(ius.object_id) as TableName,
                             si.name as IndexName,
                             si.index_id as IndexID,
                             ius.user_seeks,
                             ius.user_scans,
                             ius.user_lookups,
                             ius.user_updates,
                             ius.last_user_seek,
                             ius.last_user_scan,
                             ius.last_user_lookup,
                             ius.last_user_update
                        FROM sys.dm_db_index_usage_stats ius
                        JOIN sys.indexes si
                          ON ius.object_id = si.object_id
                         AND ius.index_id  = si.index_id
                       WHERE database_id = db_id()

Open in new window


The view also has some additional information on index usage, including the number of scans, seeks, and lookups performed on each index - super helpful information if you're looking for unused indexes or which objects are heaviest-hit. You can use it to identify which indexes are being most heavily leveraged, and which could be good candidates for removal (if they aren't being used at all by anybody in the database). However, it will also show you table-level activity as well - if you look at indexes 0 and 1 (zero is the table heap, 1 is the clustered index if one exists), you'll see activity on the underlying table data itself.

When the run the above query, you'll get results similar to the following:

Results.GIF
In the results, you'll have the following columns: 

  1. TableName - The name of the table (the easiest column)
  2. IndexName - when populated, the name of the index. When it's NULL, it refers to a HEAP - a table without a clustered index
  3. IndexID - If this is 0, it's a HEAP (IndexName should also be NULL in these cases). When 1, this refers to a clustered index (meaning that the activity columns still all refer to the table data itself). When 2 or greater, this is a standard non-clustered index.
  4. User activity (the number of times each type of operation has been performed on the index/table):
    1. User Seeks - searched for a small number of rows - this is the most effecient index operation.
    2. User Scans - scanned through the whole index looking for rows that meet the WHERE criteria.
    3. User Lookups - query used the index to find a row number, then pulled data from the table itself to satisfy the query.
    4. User Updates - number of times the data in this index/table has been updated. Note that not every table update will update every query - if an update modifies a column that's not part of an index, the table update counter will increment, but the index counter will not
  5. User activity timestamps - these show the most recent occurance of each of the four types of "User" events

Making the leap to "transactions per second"
 
The counters are reset every time the database instance is restarted, and while they're great for showing you the overall level of activity on an object, it's harder to judge the level of activity over a certain timespan. To do that, you need to take a snapshot of the view, wait a bit, and then take a second snapshot and do a comparison. To see row updates per second for every table in the database, rather than run that select over and over (and compare the results), you can use the following script and run it repeatedly to refresh the results:
 
SET NOCOUNT ON
                      
                      -- Remove the working table if it already exists
                      -- so it doesn't get in the way
                      IF OBJECT_ID('tempdb..#TableActivity_After') IS NOT NULL
                      DROP TABLE #TableActivity_After
                      
                      
                      -- Collect our working data
                      SELECT object_name(us.object_id) as TableName,
                      		user_updates as UpdatedRows,
                      		last_user_update as LastUpdateTime
                      INTO #TableActivity_After
                      from sys.dm_db_index_usage_stats us
                      join sys.indexes si
                      	on us.object_id = si.object_id
                      	and us.index_id = si.index_id
                      where database_id = db_id()
                      and user_seeks + user_scans + user_lookups + user_updates > 0
                      and si.index_id in (0,1)
                      order by object_name(us.object_id)
                      
                      
                      -- Figure out if we're running it the first time or again
                      -- Put the data into the correct tables 
                      IF OBJECT_ID('tempdb..#TableActivity_Before') IS NULL
                      BEGIN
                      	-- First time it's being run - stage the existing data
                      	PRINT 'Initial table usage collected - execute again for changes'
                      
                      END
                      ELSE
                      BEGIN
                      	-- Running script a subsequent time
                      	-- Compare this set of data to our last set
                      
                      	-- See how long it's been since we ran this script last
                      	-- Or at least since last change in any table in the database
                         DECLARE @SecondsSince DECIMAL(10,2)
                      	SELECT @SecondsSince = CONVERT(FLOAT, DATEDIFF(ms, MAX(LastUpdateTime ), GETDATE()))/1000
                      	  FROM #TableActivity_BEFORE
                      
                      	SELECT @SecondsSince as 'Seconds since last execution'
                      
                      	-- Do actual table comparison and give results
                      	SELECT a.TableName,
                      		   a.updatedrows - isnull(b.UpdatedRows,0) as RowsUpdated,
                      		  CONVERT(INT, (a.updatedrows - isnull(b.UpdatedRows,0)) / @SecondsSince) as RowsPerSecond
                      	 FROM #TableActivity_After a
                      	 LEFT
                      	 JOIN #TableActivity_Before b
                      	   ON b.TableName = a.TableName
                          WHERE a.updatedrows - isnull(b.UpdatedRows,0) > 0
                      	ORDER BY RowsUpdated DESC
                      
                      	
                      END
                      
                      -- Swap the tables so the AFTER table becomes the new BEFORE
                      -- Then clean up AFTER table since we'll get a new one next time
                      IF OBJECT_ID('tempdb..#TableActivity_Before') IS NOT NULL
                      DROP TABLE #TableActivity_Before
                      
                      SELECT *
                        INTO #TableActivity_Before
                        FROM #TableActivity_After
                      
                      DROP TABLE #TableActivity_After

Open in new window


Running this script the first time will grab an snapshot of table activity, and then it will quit with the following message:


Initial table usage collected - execute again for changes

Running it again will tell you how long it's been since it was last executed, list the tables that have changed since that execution, how many total rows have been updated, and the number of rows updated per second:

RowsChanges.GIF
Each time you execute the script beyond that will give an updated view of database activity since the last execution - since it uses the ending point of the previous execution as the starting point of each new execution, it will display only the changes since it last run, not since it was first run.

Conclusion and additional considerations

There are a number of other dynamic management views in SQL Server that can give you loads of performance information about your databases and instance - you haven't used them yet, I encourage you to read some of the online tutorials or one of the other articles here at Experts-Exchange. I've found sys.dm_db_index_usage_stats to be one of the most useful, but together, they provide some incredible detail on your server's status

Additionally, keep in mind that this view provides usage details for all indexes - not just the tables themselves. If you wanted to see activity on all indexes, you can make the following three modifications to the script:
 

  1. Add the index name (si.name AS IndexName) to the initial fetch query
  2. Remove the "WHERE si.index_id in (0,1)" in that initial query (to show all indexes)
  3. Add "IndexName" to the results statement near the end
If you have any other suggestions or questions about the script, please leave comments below!
4
21,937 Views
Ryan McCauleySenior Data Architect
CERTIFIED EXPERT
I'm a lover of data - management, querying, and presentation. I'm happy to help make things reliable, quick, and arranged to tell a story.

Comments (5)

Tom KnowltonWeb developer

Commented:
I am wondering ... can I find out which stored procedure(s) modified a particular table during a certain time frame?

I do not mean using SQL Profiler.

I mean like this:  Table has 4 rows at 1:30 PM.  Table has 5 rows at 2:00 PM.  Which stored procedure did the insert?

Can this be gathered and tracked in a log?
Ryan McCauleySenior Data Architect
CERTIFIED EXPERT

Author

Commented:
Unfortunately, there's no way I know of to track what modified the database at that time without using something like profiler to do it. The only alternative I could think of is to have every SP you execute log itself into some kind of table and then compare the timestamp of the change to what was running at that time. However, I don't know of an easy way to enforce that either.

Sorry!
Tom KnowltonWeb developer

Commented:
Unfortunately, there's no way I know of to track what modified the database at that time without using something like profiler to do it

Understood.
Thank you for your time spent researching and presenting this article.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Voted yes.  This article was very useful for me as I've been asked to come up with a list of unused databases / tables in preparation for an AWS migration.  Thanks for writing..
Good info!
But there is a problem in the query, you missed to insert the "index_id" into the table the and the use it to do the join!

-- Do actual table comparison and give results
      SELECT a.TableName,
               a.updatedrows - isnull(b.UpdatedRows,0) as RowsUpdated,
              CONVERT(INT, (a.updatedrows - isnull(b.UpdatedRows,0)) / @SecondsSince) as RowsPerSecond
       FROM #TableActivity_After a
       LEFT
       JOIN #TableActivity_Before b
         ON b.TableName = a.TableName  AND b.index_id=a.index_id !!!!!!!!!!!
    WHERE a.updatedrows - isnull(b.UpdatedRows,0) > 0
      ORDER BY RowsUpdated DESC

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.