SQL Server Performance Forum – Threads Archive

Comparing Performance Analysis of two Stored Proc

Hi members, How do I execute the showplan to get the overall cost of the procedure, if the stored procedures uses temporary tables.I tried using CTRL+L option available in the SQL Query Analyzer 2000, I could get the cost of the procedure using CTRL+K but the result shown was in parts which doesn’t enable me to estimate the overall cost. Thanking you in anticipation. -Ajay

Can you show the text format of that execution plan here. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

if the sproc has temp tables, you can create the temp tables first even though the procedure itself would normally create the table, then ctrl L, or you can change the temp tables to table variables, from which you can Ctrl L with out the hassle

Hi there,
isnt there any other or reliable way to compare two procs performance simultaneously…
ctrl L is one thing, i dont know how far it is reliable,
if i clean buffer everytime i use profiler even then it shows shows different durations for the same proc at two instances of time

This is one way i check performance of my query dbcc dropcleanbuffers
dbcc freeproccache set statistics IO on
set statistics time on
exec proc1 result it shows: (what i should infer from every occurance of "SQL Server Execution Time" which is occuring so many times )
—————
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 297 ms, elapsed time = 1341 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘SYSTEM_SETTINGS_FIELD’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0.
Table ‘ORGANISATION’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 19 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

You may add checkpoint statetment in front just to be sure nothing stayed in the cache (assumming you use test db server for that, not production one).
checkpoint
go
dbcc dropcleanbuffers
dbcc freeproccache set statistics IO on
set statistics time on
go exec proc1
go

my mail server was down , i will test it , thanx

]]>