September 2, 2010 at 3:20 pm
This just started recently on a few procedures that are running for a BI team. When a procedure via SSIS it will hang forever in the preexecute phase. When I run it locally it executes in the expected amount of time. After a lot of research I noticed there are multiple execution plans for this procedure by executing
SELECT * FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as QP
WHERE QP.[dbid] = (SELECT DB_ID('database'))
AND QP.objectid = (select object_id('procedure))
After more recearch I learned that adding Option(recompile) to the procedure It will run in the expected time frame via SSIS. Does anyone have any idea how to get this to stop? Any help would be appreciated.
Thanks in advance
September 2, 2010 at 3:53 pm
Does the proc contain any sort of conditions to execute one query v. another query depending on the conditions?
This can cause multiple execution plans. For instace, MS Crm does this alot with some procs where they have 28 conditions and 28 different insert statements to go along with the conditions (catch-all procs). This causes 28 different execution plans in the db and has caused us some slowness.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 2, 2010 at 4:03 pm
In this case it does not. I have seen that in the past and would expect that behavior. This time it is one select quite big though.
Thanks
September 3, 2010 at 7:34 am
Different ANSI settings could result in multiple execution plans.
"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
September 3, 2010 at 9:07 am
Grant, can you elaborate more on this please?
September 3, 2010 at 10:36 am
There are all types of settings that occur when you connect to SQL Server from any client. There's SET ANSI_NULLS, SET ARITHABORT, etc. If these are set differently from different connections, there could be different execution plans for the same query.
September 3, 2010 at 1:12 pm
Sorry, I've been in meetings all day.
Steve already nailed it. Thanks Steve.
"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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply