So, as the title says, I have a query that is run by SQL Server Reporting Services
, when it ran by SSRS
, I can see in SQL Profiler
it's executed using RPC Protocol
, and takes about 30 seconds. When I execute the same query (I copied it from Profiler to be sure) over SQL Management Studio
, it executes instantly (less than 1 second).
Everything is in the same server, SSRS
, Management Studio
, Profiler
and database instance, so I'm also guessing network latency should not be the culprit
The query is common SQL Text and not a Stored Procedure. I also removed the parametrization of the query, and I hard-coded the Id in the query. The same issue persists, slow on SSRS
, fast on SSMS. So I don't think it's a problem of parameter sniffing. My query is very simple, a couple joins and I'm filtering the main table by Primary Key
. The only "non-standard" feature it uses, is "WITH XMLNAMESPACES" clause, that I use to select a couple XML values using the syntax s.MyXMLColumn.value('(/Details/MyValueHere)
Using SQL Profiler
I compared CPU, Read and Duration, only the Duration column has a higher value on the slow execution. I also tried to compare the executions plans using SQL Profiler
and in the diagram they are equal, but when comparing the XML there are some differences, that I am not sure if they matter or not. Also the section RunTimeInformation
is repeated several times in several places in the plans
Plan for RPC:
<QueryPlan DegreeOfParallelism="0" MemoryGrant="3040" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="475" CompileCPU="471" CompileMemory="12832">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040" RequiredMemory="512" DesiredMemory="3040" RequestedMemory="3040" GrantWaitTime="0" GrantedMemory="3040" MaxUsedMemory="24"/>
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
<RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
...
...
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
</RunTimeInformation>
...
...
Plan for SQL:BatchCompleted
<QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="484" CompileCPU="484" CompileMemory="12832">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040"/>
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
<RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
...
...
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
</RunTimeInformation>
...
...
I am no DBA, so I'm unsure where else to look. So the question is, how do I find the cause of performance differences between the same query executed over RPC and SQL Batch?
Edit: Additional things I tried based on answers/comments:
SET ARITHABORT OFF
makes no difference
Best Answer
This makes me think it is a parameter sniffing problem, I would recommend reading
Slow in the Application, Fast in SSMS? Understanding Performance MysteriesSQL text by Erland Sommarskog, SQL Server MVP. It talks about the problems you can run into when things run almost instantly in SSMS but not in the application.