2

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
  • Compare the execution plans. If you can upload them publicly, put them at PasteThePlan.com and share the links in your question. Just be aware that the plan may contain sensitive table names or field names or query data. – Brent Ozar Nov 3 '16 at 20:32
  • Hi Brent, the plans are actually equal in the diagram. I just took them as XML from the Profiler and run a Diff tool, only stated parts are different - But I don't know what they mean – RMalke Nov 3 '16 at 20:34
3

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.

1

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.

  • Hi Cody, yes, I have tried that, the result is still the same => less than 1 sec – RMalke Nov 3 '16 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.