Sql-server – SQL Server: Find out unused queries

querysql server

I have a database with hundreds of queries created by many people over the years. Now I can investigate for each query if any SSRS report, application for the database or other queries are using each query manually, but as everyone can imagine this is penal labor…

So I found some articles in the web about unused tables or procedures.
So as list all queries with

SELECT * FROM sys.all_objects WHERE type = 'V' AND is_ms_shipped = 0

Can I somehow find out if this query is ever been called in a period of time?

E.g. since start which I can get via

SELECT sqlserver_start_time  AS LastSQLServiceRestart FROM sys.dm_os_sys_info

then I would take this date and list all views that aren't used in this time until now.

Best Answer

_{Community wiki answer:}

In general, nothing exists built in that automagically keeps count like this. Some people tend to use indexes on a table as last access but that's sketchy at best.

The honestly best thing you can do is create an audit, trace, or extended event session and garner the object usage from that. This will also require extra work on things such as stored procedures where the procedure is executed and then may touch multiple or no tables, views, etc., based on logical flow.

Related Solutions

Sql-server – Database under heavier load — all SQL queries start timing out after a period of hours/days

I was setting indices on my PKs, but I did not have indices on my FKs. Most of my queries were of the form: "Select Playlists p where p.UserId = 'XXX'"

Even though I had an index on User's ID, I did not have one on Playlist's UserID. This was root cause.

Sql-server – Deadlock with simple queries in sql server 2008

Based on what I see in SQL Sentry Plan Explorer PRO*, looks like two different jobs are fighting over who is going to get to delete some rows (click to enlarge):

Maybe there is some overlap because Microsoft does some not-so-optimal things in msdb.dbo.sp_syscollector_purge_collection_logs, for instance letting each invocation of the procedure grab TOP (@delete_batch_size) rows with no ORDER BY or exclusion techniques, meaning two people calling the stored procedure could certainly end up trying to delete rows on the same page, or maybe even the same rows.

So the easiest solution:

Don't have two jobs running the same cleanup procedure.

If you need to run both jobs (again, please explain why?), my first thought would be to set MAXDOP to 1 for the delete queries in that stored procedure (yes, you can modify it, just keep that in source control because your change could be undone by service packs, upgrades, etc).

DELETE ... OPTION (MAXDOP 1);

I don't know for sure that this is the cause; would be interesting to see an actual plan from calling the procedure manually outside the context of the job.

If you don't want to modify the stored procedure, you have at least one other option if you are running Enterprise Edition: Resource Governor. Just have the login the job executes as belong to a workload group with max_dop set to 1. I have instructions for doing so (framed in the context of maintenance jobs, but same concept) in my white paper, Using the Resource Governor.

_{* Disclaimer: I work for SQL Sentry.}

Best Answer

Related Solutions

Sql-server – Database under heavier load — all SQL queries start timing out after a period of hours/days

Sql-server – Deadlock with simple queries in sql server 2008

Related Question