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.
However, as of SQL Server 2008, there's a handy dynamic management view called sys.dm_db_index_usage_stat
s 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:
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,
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()
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:
In the results, you'll have the following columns:
Making the leap to "transactions per second"
- TableName - The name of the table (the easiest column)
- IndexName - when populated, the name of the index. When it's NULL, it refers to a HEAP - a table without a clustered index
- 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.
- User activity (the number of times each type of operation has been performed on the index/table):
- User Seeks - searched for a small number of rows - this is the most effecient index operation.
- User Scans - scanned through the whole index looking for rows that meet the WHERE criteria.
- User Lookups - query used the index to find a row number, then pulled data from the table itself to satisfy the query.
- 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
- User activity timestamps - these show the most recent occurance of each of the four types of "User" events
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
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
-- First time it's being run - stage the existing data
PRINT 'Initial table usage collected - execute again for changes'
-- 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
SELECT @SecondsSince as 'Seconds since last execution'
-- Do actual table comparison and give results
a.updatedrows - isnull(b.UpdatedRows,0) as RowsUpdated,
CONVERT(INT, (a.updatedrows - isnull(b.UpdatedRows,0)) / @SecondsSince) as RowsPerSecond
FROM #TableActivity_After a
JOIN #TableActivity_Before b
ON b.TableName = a.TableName
WHERE a.updatedrows - isnull(b.UpdatedRows,0) > 0
ORDER BY RowsUpdated DESC
-- 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
DROP TABLE #TableActivity_After
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:
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_stat
s 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:
- Add the index name (si.name AS IndexName) to the initial fetch query
- Remove the "WHERE si.index_id in (0,1)" in that initial query (to show all indexes)
- Add "IndexName" to the results statement near the end
If you have any other suggestions or questions about the script, please leave comments below!