July 6, 2021 at 3:28 am
Hi,
I want to check the view usage within a database.
I want to analyze whether a view is currently being used by any user, if yes, than when was it last accessed, how frequently and by which users.
Any pointers will be of great help.
July 6, 2021 at 10:59 am
The only sure fire way to get this done with a high degree of accuracy would be to use Extended Events. You could capture batch executions and filter for the name of the view (I'd also filter for the database that contains the view, and make that filter first so you reduce the overhead of the Extended Events session). You'll also want to capture procedure calls that reference the view. Set all that up, and you can see when and how the view is in use.
Alternatively, to at least get an idea of view usage, you can query the DMVs to see what queries currently in cache have reference the view. This is going to be radically less precise since queries may not make it into cache or may age out of cache.
Another alternative, only available 2016 and up, would be to turn on Query Store and then search through it to look for references to the view in question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2021 at 5:34 pm
xEvents is what I'd use. Get an idea of how often the view is queried with a session that is limited to just that object.
July 7, 2021 at 4:35 am
Hi,
Thanks for the suggestion. I created the extended event successfully but while accessing it , it is throwing me the error: "The storage failed to initialize using the provided parameters. "The extended event session named "ViewTrace" could not be found. Make sure the session exist and is started.
Any idea how to resolve this?
July 7, 2021 at 5:56 am
One more option is to use Audit. I have to admit that I didn't use it for a very long time (few years). The advantage that it has is that if I want to audit a specific table, I define it, and it will catch the activation of all objects that reference this table. For example if I want to audit anyone that wants to view a specific table, I only define the the table in the audit specification. If there is a view that reference this table and a stored procedure that reference the view and someone runs the stored procedure, I will see it in the audit file. When I played with it (again that was few years ago so I don't remember much of it), the only way that I could view data without the audit catching me was using DBCC Page and view the table's pages directly:-).
Adi
July 7, 2021 at 11:55 am
Hi,
Thanks for the suggestion. I created the extended event successfully but while accessing it , it is throwing me the error: "The storage failed to initialize using the provided parameters. "The extended event session named "ViewTrace" could not be found. Make sure the session exist and is started.
Any idea how to resolve this?
From the sounds of it, you're output target isn't configured correctly. Hard to say what the solution is without seeing what you're doing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply