Extremely Slow SSIS Query
<p>Just as I thought I was getting good with SSIS. </p>

<p>Within an SSIS package, I am trying to use my version of Pinal Dave's XML Plan Cache Query found here: <a href="http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/" rel="nofollow">http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/</a></p>

<p>My version just adds a SELECT TOP 50 with an ORDER BY estimatedcost DESC. </p>

<p>My query:</p>

<pre><code>WITH XMLNAMESPACES(DEFAULT  N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
PlanHandle, 
QueryText,
QueryPlan,
CacheObjectType,
ObjectType)
AS
(
SELECT
RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT TOP 50
PlanHandle,
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
QueryText,
CacheObjectType,
ObjectType,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan'
ORDER by estimatedcost desc;
</code></pre>

<p>In SSMS this query runs in the sub 30 second range but when I execute it in the SSIS package it takes 3-4 hours to complete, if it completes at all before maintenance trumps it. </p>

<p>The SSIS package executes at night when the server has very little load and then exports the results to a CSV text file. </p>

<p>The package seems to do fine until it gets to the execution of the query and then it just hangs, for hours. </p>

<p>Server Stats:
Server 2008 (64 bit)
SQL Server 2008R2 (64 bit)
24 GB RAM (16 to SQL, 8 to OS)
E5620 @2.4 GHz (2 procs)</p>

<p>Edit:<br>
Things that I have tried:</p>

<p>--Writing the output to a global temp table (and a regular table) and then querying that table for the output.<br>
--Within the SSIS package, creating and then executing a stored procedure with the query that outputs to a temp table (and a regular table) and then querying that table for the output.</p>