Empty Last Execution Time of Stored Procedure – What It Means

sql-server-2008stored-procedures

I recently ran some scripts to get the last execution time of my DB stored procedures, and the code is below:

SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',   
d.cached_time, d.last_execution_time, d.total_elapsed_time,  
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],  
d.last_elapsed_time, d.execution_count  
FROM sys.dm_exec_procedure_stats AS d  
where  DB_Name(d.database_id) = 'MyDB' 
ORDER BY [proc name] ;

But I noticed that some of my stored procedure are not listed. Then I use the code below to check particularly one of them:

select s.last_execution_time
from sys.dm_exec_procedure_stats s
where OBJECT_NAME(object_id, database_id) = 'my_stored_procedure'

and I get empty result (but nothing shows in the column).

Just want to know if this means the stored procedure has never executed before, or something else?

Thank you

Best Answer

sys.dm_exec_procedure_stats is not a persisted dataset.

It only shows details for queries that currently exist in the Cache. If the SP hasn't been run for some time then the SP will be removed from the cache in favour of more frequently executed queries.

Also the Cache can be emptied by events such as a Server Reboot, and if the SP hasn't been run since the reboot then it won't be there.

Related Solutions

Sql-server – Save Transaction: Is the name local to the stored procedure or not

No they aren't local to a stored procedure.

If you run the following in a database with simple recovery model

checkpoint
begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;

select Operation,
       Context,
       [Savepoint Name],
       [Transaction Name] ,
       Description 
from sys.fn_dblog(NULL,NULL)

It gives these results

+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     Operation      | Context  |  Savepoint Name   | Transaction Name |                                                                                               Description                                                                                                |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOP_BEGIN_CKPT     | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_END_CKPT       | LCX_NULL | NULL              | NULL             | log_minRecoveryLsn 00000112:00001ce6:008e;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000 |
| LOP_BEGIN_XACT     | LCX_NULL | NULL              | user_transaction | user_transaction;0x010500000000000515000000007dfcff481d5bed8a729370ee030000                                                                                                                              |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             |                                                                                                                                                                                                          |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_MODIFY_ROW     | LCX_HEAP | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_LOCK_XACT      | LCX_NULL | NULL              | NULL             | COMPENSATION                                                                                                                                                                                             |
| LOP_COMMIT_XACT    | LCX_NULL | NULL              | NULL             |                                                                                                                                                                                                          |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The [Savepoint Name] recorded in the transaction log does not contain any unique identifier related to stored procedure.

If you remove the rollback tran the_constant_name; from foo_test_tran_inner then the result of the final select changes to Inner, before savepoint showing that the rollback tran the_constant_name; executed from foo_test_tran_outer just rolls back to the most recent (unrolled back) savepoint of that name and takes no account of stored procedure nesting.

Sql-server – Error handling in container procedures

I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.

I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple THROW; (no other arguments) to re-raise the original exception.

Best Answer

Related Solutions

Sql-server – Save Transaction: Is the name local to the stored procedure or not

Sql-server – Error handling in container procedures

Related Question