Assess performance impact when using local variables inside stored procedures

When we need to use local variables inside stored procedures and use them as predicates in queries involving large tables, make sure that performance impact of the same is assessed correctly.

While we can’t completely avoid using local variables, we should test the respective SQL in 3 different ways to determine if there are significant performance issues due to local variables.

Test case 1

Use literal and test the query

Test case 2

Create a stored procedure with local variable definition and respetive SQL in body of the SP and test by running the stored procedure

Test case 3

Remove local variable and directly use the parameter passed to the stored procedure as a predicate for the query.

In usual scenarios, Test case 3 will perform better and most preferred.

More information at below URLs.

 
Advertisements

Identify waiting SPIDs and reasons for wait

SELECT wt.session_id, ot.task_state, wt.wait_type, wt.wait_duration_ms/60000,
wt.blocking_session_id, wt.resource_description, es.[host_name], es.[program_name]
FROM sys.dm_os_waiting_tasks wt INNER JOIN sys.dm_os_tasks ot ON
ot.task_address = wt.waiting_task_address INNER JOIN
sys.dm_exec_sessions es ON es.session_id = wt.session_id WHERE
es.is_user_process = 1

SQL Profiler trace file analysis

To read a profiler trace into a table

SELECT * INTO #trc
FROM fn_trace_gettable(‘c:\temp\trace_file_name.trc’, default)
Top time-consuming stored procedures in the trace
SELECT objectname,
SUM(Datediff(SECOND, starttime, endtime)) total_duration_seconds,
COUNT(*)  runcount,
MAX(Datediff(SECOND, starttime, endtime)) maxruntime_seconds
FROM #trc
WHERE eventclass = 43
GROUP BY objectname
ORDER BY SUM(Datediff(SECOND, starttime, endtime)) DESC

Top time-consuming SQL code in the trace

SELECT Substring(textdata, 1, 1000) ,
objectname,
SUM(Datediff(SECOND, starttime, endtime)) total_duration_seconds,
COUNT(*)  runcount,
MAX(Datediff(SECOND, starttime, endtime)) maxruntime_seconds
FROM #trc
WHERE eventclass = 45
AND Substring(textdata, 1, 4) <> ‘exec’
GROUP BY Substring(textdata, 1, 1000),
objectname
ORDER BY SUM(Datediff(SECOND, starttime, endtime)) DESC