Stored Procedure - Usage

Question

Stored Procedure - Usage

pdsqsql

Old Hand

Points: 358
More actions
April 9, 2020 at 5:47 pm

#3741845

Hi,
I am running query using sys.dm_exec_procedure_stats to get the stored procedure usage report and little confused that it shows latest time like today but Execution count is NULL so what i should understand?
Is it Not using Stored Procedure at all since Last Sql server Restarted?
How I should interpreting Last_Execution_Time and execution_count columns as 4/1/2020
NULL

Viewing 9 posts - 1 through 8 (of 8 total)

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

BTylerWhite SSCommitted Points: 1569 More actions · Answer 1

I don't think the sys.dm_exec_procedure_stats view can return a NULL execution_count. It's definition represents that column as non-nullable.

Are you sure you're querying the view correctly? Do you still see the same results for that procedure using the following query?

SELECT DB_NAME(PS.database_id) AS DatabaseName,
       OBJECT_NAME(PS.object_id, PS.database_id) AS ObjectName,
       PS.last_execution_time,
       PS.execution_count
FROM sys.dm_exec_procedure_stats AS PS;

I know procedures only appear in this view if they're cached. The procedure must also finish before the view is updated. I don't think that procedures using a "WITH RECOMPILE" hint appear here in this view, either.

pdsqsql Old Hand Points: 358 More actions · Answer 2

Thanks for your response.

I am using in my stored procedure which creates temp tables and then loading into table for all the Databases in the server so in that some where issue looks like.

I am trying to get all the UnUsed procedure list for all the databases into one query so during migration we can eliminate those procedures/objects.

Do you have any better way to do this?

Thanks for your help!

BTylerWhite SSCommitted Points: 1569 More actions · Answer 3

So you're iterating over each database to identify the procedures in each database to compare to your findings in sys.dm_exec_procedure_stats? I think setting this up as a job to persist the results in a table would work to identify procedures that aren't being used. It may be better just to capture the results periodically from sys.dm_exec_procedure_stats without checking each database independently to reduce the load. This way, when you're ready to migrate you can do a comparison prior to make sure these are actually unused. It's likely there could be some procedures that execute monthly or quarterly.

You could also try setting up an Extended Events session to capture the sqlserver.module_start event. This way, if there are any procedures with recompilation, they would be less likely to be missed from sys.dm_exec_procedure_stats.

There was a question asked a few years ago on StackExchange related to this, hopefully it can offer some additional assistance.

pdsqsql Old Hand Points: 358 More actions · Answer 4

Thanks once again.

Right, i am doing for each DB, Actually i am using to get for all the database in one procedure call and storing into table and running the job everyday but as you mentioned earlier that NULL shouldn't be possible so looks like somewhere issue in my procedure.

So you are suggesting to do individually for each DB and only just use the sys.dm_exec_procedure_stats, i can try that way too as i started previously this way but i thought running for each DB instead let me capture for all the DB together as i did the same for Tables.

We have Sql 2008 so let me check Extended Events supported or not as i thought it's started in Sql 2012.

Thank you!

BTylerWhite SSCommitted Points: 1569 More actions · Answer 5

You're welcome.

This was posted in the SQL Server 2014 forum. Extended Events were introduced in SQL Server 2008, but is more difficult to setup as there isn't a GUI in that version. There were a lot of quality-of-life enhancements to Extended Events in SQL Server 2012.

I probably wouldn't try to iterate over each database in the job, I would simply try to capture the minimum viable information from the sys.dm_exec_procedure_stats view and store this in a table. Definitely make sure you're capturing the database_id and object_id to be able to do those comparisons. When you're ready to migrate, you could take those results and then compare to procedures in each database. Just make sure you make sure the procedures aren't being recompiled or else they wouldn't show up in your results as I mentioned before.

pdsqsql Old Hand Points: 358 More actions · Answer 6

Thanks once again Tyler for your input.

I think it's a good idea to store the result in a one central table and run the daily job for each DB.

Although it's a very clean approach but Only the issue with this is i have to set up the daily sql Agent job for each database then it will multiple jobs when you have multiple databases in a server, right?

BTylerWhite SSCommitted Points: 1569 More actions · Answer 7

You could use the undocumented sp_MSforeachdb procedure to iterate over each database. There are some potential bugs and quirks with this, so I would encourage you to read into it a bit more. You can take a look at this one: Making a more reliable and flexible sp_MSforeachdb.

What I was really suggesting is that you create a single job offloading the results from that DMV. No need to go through each database for that, as long as it's capturing the database_id and object_id.

Later on, you can compare those to sys.procedures in each database. Unfortunately, there isn't a real "silver bullet" approach that I've found to take care of this. You should also try to check with your lines of business or other developers to ensure they aren't really needed.

pdsqsql Old Hand Points: 358 More actions · Answer 8

Thanks once again Tyler.

Let me build the query to store the data from the sys.dm_exec_procedure_stats DMV view and store into table.

Thank You!