Hi experts,
I have lots of views and some may no longer be used. I don't know of a way of retrieving the date a view was last ran. Is there a way? This is MSSQL 2012.
Thanks very much.
No, SQL doesn't store that kind of info. For one thing, the overhead would be huge.
2 Likes
and even if there was one, it would be very misleading as some views might be used for example for some yearly report. then you say it has been a year since we used this view, remove it. Then end of year a report fails somewhere etc.
from the thread of questions you are asking, it sounds like you are trying to figure out the slow views but you have so many you are not sure which one is really used. trying to wrangle these one off sounds tedious and prone to problems.
I recommend you use a more holistic approach from all of the moving pieces in your ecosystem that uses these views and do a full, well documented audit.
3 Likes
You are close, yosiasz. That is one goal. Another is to determine the commonality between the tables so I can archive data. Most of the tables don't have a Date column that reflects the age of the row. It's messy. Do you know of any software that can help or can I use a DIY approach? I've built simple data dictionaries in the past but this effort is more involved. Thanks for any ideas.
A lot of people poo-poo the use of SQL Profiler. If you don't know how to use a nice, server-side profile run, then try to use Extended Events to do the same thing and that "thing" would be to simply look for the name of a small handful of view in the "text" column especially during a week when the month end occurs. If they don't get used then, "disable" them by renaming them with a date to be dropped and wait for a year if you really want to be on the safe side. If someone squawks about a missing view, the find that "disabled" view and simply rename it so that it no longer contains the date to be deleted in the name.
1 Like
Do you have a version of the app or whatever uses these views in a pre-prod environment with the database(s) restored from prod?
like @JeffModen says, you can start renaming views and see what breaks in this preprod environment by moving all the possible levers of the apps/reports that might use these views.
@DBAforever following the bread crumbs of your posts, your issue might be with the Access tool that is probably making unlimited calls to views. This access app probably worked fine few years back. but now that the data has increased the app is unable to scale