Sql-server – How to get the whole execution log of stored procedure

sql-server-2008sql-server-2008-r2

I know that how many times it was executed from execution_count in sys.dm_exec_procedure_stats.But how these values are Calculated? Is there any way to know how these values get calculated? I want whole log when the stored procedure was first time executed to till last_execution_time logs.

Best Answer

But how these values are Calculated? Is there any way to know how these values get calculated?

Answer to this can be refer from MSDN stating below

sys.dm_exec_procedure_stats

Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats

You can run something like below code to collect the data from this dmv as per the load to analyse the usage of resources during the execution of that SP:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME] 
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats 
ORDER BY AVG_LOGICAL_READS DESC

You can modify the query above and gather the data to collect the metrics for the SP by scheduling it via SQL agent job. After a certain interval you will have sufficient data to make analysis. An example of such is illustrated here.

Hope this helps!

Related Solutions

SQL Server – Automatically Execute Stored Procedure After RESTORE DATABASE Event

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

Sql-server – SQL Trigger to verify account, if not verified dont process stored procedure/transactions

No you cannot use Triggers for this because Triggers do not fire in response to Read-type events (Execute or Select). Nor should you because, in general, triggers are not an appropriate device for implementing security.

If you want to implement your security token idea on SQL Server(*) there are several general ways to go about it:

Alter your Stored Procedures: Add an additional parameter to your stored procedures, then have your stored procedures themselves check this. This is the recommended method.
Wrap your Stored Procedures: If you do not want to alter all of your stored procedures, you could instead do as one of the commenters suggests and write wrappers for all of your stored procedures. The wrapper has the token as an additional parameter, which it first checks and then calls the actual procedure. You use SQL-object security to prevent the actual procedures from being executed by anything other than the wrapper procedures. This approach is not usually recommended because of the extra work and its higher maintenance. However, this can be alleviated through automation if your database's security, schema and procedure design is structured correctly for it (this is how I do it, though not for this purpose).
A variant of #2 (above) would be to have a single Wrapper Procedure that calls all of the other procedures. This would require that the first two parameters were the token and the actual procedure name and the rest would be the actual parameters shifted over two places. The Universal Wrapper would then check the token and if valid, then call the actual procedure. This is actually surprisingly easy to do (and does not require dynamic SQL either), however, it does also require properly designed security schemas to enable closing other security holes. This approach is not usually recommended because of it's even higher dependence on properly designed security and schema structures and because auditors do not generally understand it and it makes them nervous.

(* - and this may well be something that is better addressed at the web/app server level, since that seems to be where the weakness is.)

Best Answer

Related Solutions

SQL Server – Automatically Execute Stored Procedure After RESTORE DATABASE Event

Sql-server – SQL Trigger to verify account, if not verified dont process stored procedure/transactions

Related Question