-
finding unused tables using monitoring tables
Hello,
This stored proc collects the information from the monopenobjectactivity table and saves the data in TablesUsed table.
CREATE PROCEDURE TablesUsed (
@wait_time char(8) = '00:10:00',
@loop int = -10
)
AS
BEGIN
SELECT * into #temp1
from master..monOpenObjectActivity
SELECT * into #temp2
from master..monOpenObjectActivity
INSERT INTO TablesUsed
(DBName,TableName,IndexID,Operations,RowsInserted, RowsDeleted,RowsUpdated,LockWaits,LogicalReads,Phy sicalReads,UsedCount,LastUsedDate,FromDate,ToDate
SELECT db_name(U2.DBID),object_name(U2.ObjectID, U2.DBID),U2.IndexID,U2.Operations,U2.RowsInserted, U2.RowsDeleted,U2.RowsUpdated,U2.LockWaits,U2.Logi calReads,U2.PhysicalReads,(U1.UsedCount -U2.UsedCount),U2.LastUsedDate,dateadd (mi,@loop,getdate()),getdate()
from #temp1 U1 , #temp2 U2
where Object_name(U1.ObjectID,U1.DBID)=Object_name(U2.Ob jectID,U2.DBID)
and db_name(U1.DBID)=db_name(U2.DBID)
and U1.IndexID=U2.IndexID
and U1.IndexID = 0
END
go
Above is the stored procedure which i wrote to monitor the monopenobjectactivity table. DBA will run this stored proc for about 2 months. then i am thinking to query the "TablesUsed" table to find the unused tables. below is the query
select * from TablesUsed
where RowsInserted = 0 and RowsUpdated = 0 and RowsDeleted = 0
order by LogicalReads asc
My question is, can i consider this result where all the RowsInserted, RowsUpdated and RowsDeleted = 0's as unused tables in the database?
when I am observing the resultset of this above query, i am observing that even though RowsInserted, Deleted or Updated are 0 but we have Operations , Logical and Physical reads on that table...so does it mean that the table is being used?
if so, can anyone please guide me, how to write the query to find the unused tables?
Thanks in advance.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|