When trying to guage the level of database activity, you can use SQL Profiler to view the type and volume of transactions in motion at any given time and to view the overall level of database IO, but you can’t use it to directly tell which database tables are being updated.
However, there’s a handy dynamic management view called sys.dm_db_index_usage_stats that tells you the number of rows that have been updated in each database index since the instance was last restarted (or since the table/index was created, if that happened more recently):
SELECT * FROM sys.dm_db_index_usage_stats[plain]
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. If you look at indexes 0 and 1 (zero is the table heap, 1 is the clustered index), you’ll see activity on the underlying table data itself.
I needed to see the row updates per second for every table in the database, so rather than run that select over and over (and compare the results), I wrote a quick script to do the comparison repeatedly for me:
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
Running that script the first time will grab an snapshot of table activity. Running it again will tell you what has changed since you ran it the first time, and running it again will continue to tell you (updating the “before” image each time so you’re getting an update on only the most recent database activity).
If you wanted to see activity on all database indexes, you could update the query at the top to show index name and remove the “WHERE si.index_id in (0,1)” and you’d see all the index details.
I hope this is helpful – if you have any feedback or would like to see something added, please feel free to leave a comment below!