February 2, 2011 at 7:30 am
When we run a stored procedure in query analyzer, it completes within fraction of seconds but when running from frontend, it takes lots of time (more than 30 seconds). What can I specifically look into outside the stored procedure ?
February 2, 2011 at 8:10 am
Are you passing it the same parameters? Are the ANSI settings the same between the app and SSMS?
"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
February 2, 2011 at 8:23 am
Yes, ANSI settings are same. I have taken the same parameters as used by application. Right now trying to go into the right direction.
February 2, 2011 at 8:25 am
Check the execution plans. See if they're different. Both tests are against the same server? If not, look for differences there. Look for blocking... Not being there, it's hard to know for certain.
"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
February 2, 2011 at 8:30 am
Is there a way to check execution plan for sproc while running it from Application ??? I've done it only from query analyzer.
February 2, 2011 at 8:49 am
Yes, you can query the DMV, sys.dm_exec_query_plan. You just have to combine it with sys.dm_exec_requests to get the plan_handle while it's running.
"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
February 2, 2011 at 10:30 am
Hello Grant, I'm really not able to figure it out, though I tried a lot. We are still facing the issue.
February 2, 2011 at 11:11 am
A query similar to this will show what you need:
SELECT deqp.query_plan
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE ...
In the WHERE clause, not sure what you have available, but you could use the Session_ID from the dm_exec_requests DMV or, you could use the stored procedure name and a LIKE statement against dm_exec_sql_text text column.
Either way, you can look at the execution plan for an operating query this way.
"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
February 2, 2011 at 12:01 pm
Thanks Grant. It certainly helped.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply