<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
18,953 Points
12,653 Views
3 Endorsements
Last Modified:
Approved
Ryan McCauley
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.
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!
3
Comment
5 Comments
LVL 5

Expert Comment

by:Tom Knowlton
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?
0
LVL 28

Author Comment

by:Ryan McCauley
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!
0
LVL 5

Expert Comment

by:Tom Knowlton
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.
0
LVL 67

Expert Comment

by:Jim Horn
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..
0

Expert Comment

by:Andrew Red
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
0

Featured Post

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month