display estimated execution plan

saloj used Ask the Experts™
I have created DB on local as we have on server and we have the following query.
SELECT buyitem.dataid, SUM(buyitem.cost_point) AS total,itemName.itemName_C,buyitem.cost_point FROM buyitem
 LEFT OUTER JOIN itemName ON buyitem.dataid = itemName.itemID WHERE (buyitem.create_time > '2010/06/01') and
 (buyitem.create_time < '2010/06/29') GROUP BY  buyitem.dataid, itemName.itemName_C,buyitem.cost_point ORDER BY  total DESC

And also attached display estimated execution plan as following.
why both looks different on server and local
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Also we web application on .net2.0 and  get error when we searched for large scale date from the web. how could it be resolved.

Do you have a different set of data on the two databases? The SQL optimiser could choose different plans if there are different amounts of data in the tables. Also if you have a different number of CPU cores between the two machines you may see a different plan.


Both databases have same data. how could it be different though both have same table design and same data. does different machine display different execution plan as i uploaded pics from server and local pc?
Do you have exactly the same build of SQL Server on both machines? You can get the version by executing SELECT @@VERSION. Different builds of SQL can yield different plans for the same query.

Also have you created and updated the statistics relevant to the tables in the query? If the statistics are inaccurate on one database, a different plan may be generated.
Top Expert 2012

>>how could it be different though both have same table design and same data.<<
But the question was do you have the same number of rows?  And more importantly have you updated statisitics in both databases.  Both of these can make a difference to the execution plan, as they should.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial