objects never being used -?

Question

Post reply

objects never being used -?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 2, 2012 at 5:08 pm

#279816

Looking over different forums i slightly tweaked the query to identify a object (table/view) on which a select ,update,delete,truncate has never been done. I do know that value from this dmv is reset when a server is rebooted. Please let me know if my approach is appropriate.
SELECT
t.name,
t.type
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update,
t.create_date,
t.modify_Date
FROM sys.dm_db_index_usage_stats i
JOIN sys.objects t
ON ( t.object_id = i.object_id )
WHERE database_id = Db_id()
and t.type !='S'
AND user_seeks = 0
AND user_scans =0
AND user_lookups = 0
AND user_updates = 0
order by name

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

Reverse the order of the tables isnmy recommendation.

Select from sys.tables where not exists in then dmv isnthe way ive done it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Lowell (8/2/2012)
Reverse the order of the tables isnmy recommendation.
Select from sys.tables where not exists in then dmv isnthe way ive done it.

Does the dmv depend on metadata local to server or local to db? I mean i have log-shipping running for every two hours, would the data be same on primary and secondary?

durai nagarajan SSCoach Points: 15775 More actions · Answer 3

DMV and data are different.

Data will be definitely the same , hope the dmv data will differ based on server and db.

Regards
Durai Nagarajan

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

durai nagarajan (8/3/2012)
DMV and data are different.
Data will be definitely the same , hope the dmv data will differ based on server and db.

I didn't quite understand your response. I was asking if i have log-shipping/mirroring setup between two dbs on two different servers and i run the query which is posted on my original post will the output be the same from two different servers? Is DMV O/p dependent on server or on the database?

durai nagarajan SSCoach Points: 15775 More actions · Answer 5

i mean when a db is restored it should have fresh dmv data be it on same server or different.

Regards
Durai Nagarajan