slow stored proc execution on log shipping secondary
SQL Build Version SP Level Edition 10.50.1600.1 RTM Standard Edition (64-bit)
Secondary DB Server 48 GB of RAM DELL PowerEdge R710 – WIN2K8 R2 Enterprise Edition (x64) Dual Socket Six Core Intel Xeon E5645 2.4GHz SQL Build Version SP Level Edition 10.50.1600.1 RTM Standard Edition (64-bit) Reporting server and sharepoint DB Server
the production server 36 GB of RAM max server memory 36864 maxdop 0
the secondary/reporting server 48 GB of RAM max server memory 36864 maxdop 0
The secondary database server is kept in sync via log shipping for reporting databases. Reporting DBs are in standby A stored proc running on reporting DB executes in 6:02 via SSMS on primary it executes :02 via SSMS
SSMS set options appear to be the same.
comparing execution plans there are row count estimates that are significantly different. ran update_statistics with full scan and waited for t log to be restored to secondary . The execution on secondary still running long. execution plans still defferent.
Any pertinent suggestions welcome.
The plans are attached.
- The plan choice is likely very sensitive to the parameter values, so there is always a possibility of compiling a plan for an atypical parameter set, which is then suboptimal for future executions with more typical values. See Conor Cunningham's post and this one from the Query Optimizer Team for more details.
- In addition to the standard parameter-sensitivity issues, you are modifying the values of the stored procedure parameters. See this Microsoft CSS post to understand why this is not a good idea.
- You may be able to choose typical values for the stored procedure parameters (values that produce a plan shape that works well for most common scenarios). If so, use a OPTION (OPTIMIZE FOR (@startDate = 'xxxx', @endDate = 'yyyy') hint on the query.
- You may like to try OPTION (OPTIMIZE FOR UNKNOWN) if no suitable specific value is available, and you want the optimizer to use average density information in choosing a plan. I think this is less likely to be optimal in your case, but that is no more than an educated guess at this stage.
- Compile time for the queries is moderately high, between 0.5 and 0.9 seconds. This is a very significant fraction of the reported 2 second run time for the fast plan, so the common solution of recompiling the statement on each call for the parameter values at the time may not work well for you. If you want to try it, OPTION RECOMPILE on the query (not WITH RECOMPILE on the procedure!) is the syntax you will need.
- Do not worry about optimizer "Time Out". This is much misunderstood by almost everyone. The optimizer tries very hard to avoid spending more time exploring alternative plans than it might save. With poor cardinality estimates due to inappropriate sniffed parameter values (@endDate = NULL), the optimizer finds plans that look cheap because of the low row count. Therefore, the TimeOut threshold is lower (it's not worth spending long on a plan that is expected to execute in a few seconds). Fix the root cause, not the symptom here. I have yet to encounter a poor plan choice caused by a TimeOut – there is always a deeper cause.
- Aside from the parameter changing issue, my other main observation is that the query plan far too large and complex. With many joins and aggregations, the optimizer relies more on heuristics than proper reasoning. More to the point, cardinality and value distribution estimations are almost certain to be no better than guesses after relatively few steps. If you want the best from the optimizer, and more importantly, reliability from it as data distributions change, you will break this query up. Materializing small sets using temporary tables typically adds very little overhead, while providing much better information to the optimizer. In my experience, writing queries that optimize well more than pays for itself in performance terms through increased plan quality and robustness in the face of change. There are other benefits too, of course, such as making the procedure more maintainable.
The parameters that you have passed in do appear to be different.
In one case the EndDate is '3999-01-30 23:59:59.000'
and in the other it is null.
Try ensuring the parameters are EXACTLY the same.