SELECT *
FROM sys.dm_db_index_usage_stats
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()
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
Initial table usage collected - execute again for changes
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.
Comments (5)
Commented:
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?
Author
Commented:Sorry!
Commented:
Understood.
Thank you for your time spent researching and presenting this article.
Commented:
Commented:
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