Is there an alternative to SET STATISTICS TIME which also shows the statements?

Question

SET STATISTICS TIME statement is only useful while developing as with it one can performance tune additional statement being added to the query or UDF/SP being worked on. However when one has to performance tune existing code, e.g. a SP with hundreds or thousands of lines of code, the output of this statement is pretty totally useless as it is not clear which to which SQL-statement the recorded times belong to.

Isn't there any alternatives to SET STATISTICS TIME which also show the Statements to which the recorded times belong to?

Answer 1

I'd create an extended events session similar to the one below:

CREATE EVENT SESSION [proc_statments] ON SERVER 
ADD EVENT sqlserver.module_end(
    WHERE ([object_name]=N'usp_foobar')
),
ADD EVENT sqlserver.sp_statement_completed(
    SET collect_object_name=(1),collect_statement=(1)
    WHERE ([object_name]=N'usp_foobar'))
ADD TARGET package0.event_file(SET filename=N'proc_statments')
WITH (TRACK_CAUSALITY=ON)
GO

This tracks both stored procedure and stored procedure statement completion for a procedure called usp_foobar. Within the event itself, there's an identifier that helps you tie together which statements were executed as a result of having executed a specific procedure (that's what the TRACK_CAUSALITY is for).

Answer 2

If your Stored Procedures are granular then you could use this DMV to get an idea of times.

SELECT 
    DB_NAME(qs.database_id) AS DBName
    ,qs.database_id
    ,qs.object_id
    ,OBJECT_NAME(qs.object_id,qs.database_id) AS ObjectName
    ,qs.cached_time
    ,qs.last_execution_time
    ,qs.plan_handle
    ,qs.execution_count
    ,total_worker_time
    ,last_worker_time
    ,min_worker_time
    ,max_worker_time
    ,total_physical_reads
    ,last_physical_reads
    ,min_physical_reads
    ,max_physical_reads
    ,total_logical_writes
    ,last_logical_writes
    ,min_logical_writes
    ,max_logical_writes
    ,total_logical_reads
    ,last_logical_reads
    ,min_logical_reads
    ,max_logical_reads
    ,total_elapsed_time
    ,last_elapsed_time
    ,min_elapsed_time
    ,max_elapsed_time
FROM 
    sys.dm_exec_procedure_stats qs

Answer 3

I would recommend to use advanced tool. Here is example of one call of sp with all and every internal details. On the right you have different runs history which can be commented and analyzed later. All you need for stats/index usage/io/waits - everything available on different tabs. Util: SentryOne Plan Explorer (free).