How often is my DB view used?

Question

How often is my DB view used?

Paul Nevlud

Old Hand

Points: 329
More actions
February 22, 2007 at 5:48 pm

#90956

I've got a view in my database that I am pretty sure is not referenced at all. I'd like to run a test on the view for a little while before dropping it though. Is there some way for me to check how frequently (if ever) the view is accessed?
Thanks
Paul

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Dirty way : rename it and wait for a complaint.

Hard way (for the server anyways) : Run a trace and scan for the use of that object. Depending on the traffic that can take much more resources than you may have to spare.

Other option is to fully scan both server side and client side code for the view name. This might work quite well depending if the view name is not general like "Orders".

They all work, they all have down sides... you have to pick your poison .

Paul Nevlud Old Hand Points: 329 More actions · Answer 2

Ya, I suspected that I'd have to do a search in the code. That will be the safest, the view does have a unique name.

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ya but it doesn't make them search safe. I had a few cases like this one :

vwOrders

and

vwOrdersDetails

If you search for vwOrders you'll find hits for both view names. That's why you have to be aware of this "flaw" and make sure you manually scan the search results. But IMHO this is one of the safest/fastest way you can have. That way you don't have a big strain on the server like the trace and you can be relatively sure you won't break anything if you remove it.

But even if I find this view to be unsued, I would first take a backup of it before just renaming it. Then after a few months without a hitch I would feel safe dropping it altogether (still having a backup of it in a safe place, because that view may be usefull to another DBA which runs it only yearly or on a needed basis).

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

I prefer a couple of slightly different methods - both call for complaints though. Method 1: revoke all permissions from the view after scripting themout first of course. Method 2: drop the view, again, after scripting it out and its permissions.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ya that's the quick and dirty way. However even my search method willeventually come to that point where you backup and test a delete. So it might be a little faster that way... even with a little less peace of mind .

Paul Nevlud Old Hand Points: 329 More actions · Answer 6

Good points. I generally do a staged removal of objects. Then I think I'll go the revoke route when pulling the views out of production. That way, if there are any problems (unexpected), enabling permissions again will be a quick way of restoring the system.