Primary DB server 36 GB of RAM DELL PowerEdge R710 - WIN2K8 R2 Enterprise Edition (x64) Dual Socket Quad Core Intel Xeon L5520 2.26GHz cluster
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.
By Joe_Hell 16 asked Feb 25, 2013 at 03:28 PM
There's only so much information to be gleaned from an pre-execution (estimated) plan, so if you would like a more comprehensive analysis, please feel free to attach post-execution (actual) plans to your question. If you are able to run the procedure from Plan Explorer, so much the better because this captures even more useful information. Anyway, comments on the plans as they are right now:
By SQLkiwi ♦ 6.6k answered Feb 25, 2013 at 04:51 PM
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'
Try ensuring the parameters are EXACTLY the same.
By Dave Ballantyne 263 answered Feb 25, 2013 at 04:14 PM