SQL Server Performance Forum – Threads Archive
Execution time for procedureGood Afternoon SQLServer Gurus, I have a question in regards to the execution time for a sql server stored procedure. For some reason if I execute the stored procedure by calling it, for ex: exec sp_…. from the query analyzer, the execution time is around 1 min. But when I take the contents of the stored procedure and execute the contents seperately in the query analyzer then it takes only 7 secs. Does anyone know why this happens? How can I correct it such that the "exec sp_…." command will take the same amount of time (i.e 7 secs). Thanks in advance,
This can happen if the SP is using a wrong execution plan. Try re-compiling the stored procedure using ‘SP_recompile’.
You may also try clearing the procedure cache using ‘DBCC FREEPROCCACHE’.
Check if the execution plan of the stored procedure is same as the ad-hoc query. Try updating statistics for the tables used in the query and re-run the same. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Also if the proc uses variables and the qa query uses constants then this can affect the plan Cheers
Yes, the execution plan was different in each case and I adjusted some indexes on the tables it is using and it works now. Thank you all. Mahesh
BTW, from Query analyzer have you tried connecting server as ‘LOCAL’ or with SERVER name. _________