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