Query over SSRS/RPC:Completed a lot slower than SSMS

Question

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

Answer 1

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.

Answer 2

Have you tried running it from SSMS with SET ARITHABORT OFF? Things can run with different default options in SSMS which can lead to different plans.