January 23, 2008 at 2:50 am
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image attached
Any ideas for this issue?
Thanks a lot
January 23, 2008 at 5:50 am
Check the connection settings for both Management Studio & the App. You may have differences there that are causing the differences in execution time. Stuff like SET IMPLICIT_TRANSACTIONS, SET ANSI_PADDING.
Do a search on the boards. There have been several detailed discussions of exactly this type of issue in the past.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 23, 2008 at 11:13 pm
Hi Grant,
Thanks for yr advice
January 24, 2008 at 10:39 am
you could also add SP:stmt starting / completing to your trace and execute it through the app and see what particular staement within the procedure is causing the delay (if there are multiple statements within the procedure ).
you could review the actual execution plan as well to see what costs the most, if table / index scans are being used on large tables etc to see if anything needs to be tweaked.
in SMS be sure you DBCC DROPCLEANBUFFERS before you run the test also so that you are sure to not be getting buffered / cached data.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply