Which table is used last

Question

Which table is used last

Viewing 7 posts - 16 through 21 (of 21 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I thought of an interesting method you could use for this if you use the RowVersion/Timestamp data type in your tables. Use a Dense_Rank() query that selects a union of all the tables you are tracking, with just the RowVersion column and a table identifier (name, object ID, whatever), ranking by table identifier. Do that in a CTE, and the outer query would select the tables you want based on the Dense_Rank column.

You'd be able to do this without a trace, any triggers, a log parser, or anything outside the tables themselves and a little bit of clever querying.

Of course, you'd have to modify all the tables, and build the query (you could do that dynamically through a single query of sys.columns), and you'd have to make sure that adding that column to every table wouldn't break anything else in your database. In other words, it would be a major undertaking in a database of any significant complexity. But it COULD be done that way.

It might not even be slow if you select the max RowVersion value from each table, instead of some huge recordset.

I haven't tested this, but theoretically, it would work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

parthi-1705 (10/27/2010)
Hi
In a Database i am having nearly 200 plus tables i need to find the tables which are used last.i.e say Table5,Table8,Table55,Table87,Table19,Table125,Table159,Table43,Table186,Table68 this are last 10 tables which has insertion or updation or deletion.Means that datamodification has been taken place in this tables i need each and every table when the data modification has been taken place.
Like: Last 10 table, used time
Table68 @ 27/10/2010 12:10:00:256 PM
Table86 @ 27/10/2010 12:09:52:506 PM
.......
Table5 @ 27/10/2010 12:03:05:549 PM
Thanks
Parthi

I may be misreading the requirements but can't you just determine when one of the indexes on the table was last used which would also indicate when the table was last accessed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 3

Jeff Moden (10/27/2010)
I may be misreading the requirements but can't you just determine when one of the indexes on the table was last used which would also indicate when the table was last accessed?

Isn't that assuming that all of your queries have an index it can run against, and/or a clustered index to run a "table scan" against?

Now, I've never run into being able determine when an index was last accessed. It doesn't show up in the sys.indexes system view. How do you do this?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Wayne, they're looking for last data modify.

Which might work, if all the fields are in an index. But afaik (assuming we CAN find the last modified date) even the clustered index won't report a change unless the column is in the index itself, not just included at the leaf level. Though non-clustered might, the data would have to be moved into the index from the main at the leaf... hrm. Interesting question.

Although, I'm with Wayne. Where do you find the 'last time index changed' date? 🙂 I'm relatively sure you can't do it in 2k, but perhaps 2k5 and 2k8 gave us more options?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (10/27/2010)
I may be misreading the requirements but can't you just determine when one of the indexes on the table was last used which would also indicate when the table was last accessed?

Yes, but that information does not persist over a server (or even DB restart). In theory it's possible, but I don't personally consider it reliable. If all that is needed is a rough guess, maybe, but too easy to miss stuff.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Craig Farrell (10/27/2010)
Although, I'm with Wayne. Where do you find the 'last time index changed' date? 🙂 I'm relatively sure you can't do it in 2k, but perhaps 2k5 and 2k8 gave us more options?

last_update in sys.dm_db_index_usage_stats. Concern is, as I mentioned, it does not persist over db/server restart and hence I wouldn't consider it reliable.

To use it you'd have to poll the table on intervals and hope you don't miss anything if the server restarts.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

GilaMonster (10/28/2010)
Craig Farrell (10/27/2010)
Although, I'm with Wayne. Where do you find the 'last time index changed' date? 🙂 I'm relatively sure you can't do it in 2k, but perhaps 2k5 and 2k8 gave us more options?
last_update in sys.dm_db_index_usage_stats. Concern is, as I mentioned, it does not persist over db/server restart and hence I wouldn't consider it reliable.
To use it you'd have to poll the table on intervals and hope you don't miss anything if the server restarts.

Indeed polling sys.dm_db_index_usage_stats and persisting that data is a valid alternative.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me