Alternative for DMV.

Question

Alternative for DMV.

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 17, 2010 at 5:31 am

#97494

i need any alternative for select last_user_seek,last_user_scan,last_user_lookup from sys.dm_db_index_usage_stats
basically i need to fetch data from last_user_seek or last_user_scan columns ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Why can't you use the DMV?

There's no really practical alternative. I suppose you could trace for the actual execution plans, load into a table and use xquery to see what index seeks and scans were done when, but that's hardly practical (large volume trace, lots of impact on server, difficult xquery to write)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

IS sys.dm_db_index_usage_stats not a DMV ?

actually , there is one column "last_user_update" which hwe are using to capture all the tables got any kind of insertion/updation/deletion on it.then we are performing some application specific task on it.

Some times i dont get relevant information from it ( actually data doesnt get populated in "dm_db_index_usage_stats" )

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Bhuvnesh (8/17/2010)
IS sys.dm_db_index_usage_stats not a DMV ?

Yes it is.

If you need to do something on insert/update/delete, I recommend triggers. That DMV is just to give info on how indexes are used.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

GilaMonster (8/17/2010)
Bhuvnesh (8/17/2010)
IS sys.dm_db_index_usage_stats not a DMV ?
Yes it is.
If you need to do something on insert/update/delete, I recommend triggers. That DMV is just to give info on how indexes are used.

we basically concern with user_last_update column to fetch the time when any particular table got any DML operation

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)