Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have two servers running SQL server 2008 R2, with identical databases, where we implemented a datawarehousing solution. The cube processing operation was taking a VERY long time in one of the servers, which led us to dig into the queries generated by SQL Server. We found the query that was taking long time, and we saw that it is generated from a Many-to-many relationship in the datawarehouse, where 6 Joins of the same table are being executed. this table contains about 4M records.

We tried to look into the execution plans of this query on both servers. In the first server, the execution plan uses parallelism and executes in 90 secs, whereas the seconds uses only sequential executions, which results in a 14 HOURS execution time.

Data in the two servers is different. The server which takes more time has more data (obviously). We tried to update statistics, rebuild indexes, re-compute execution plans, copy statistics from one server to the other, but no result!

Hope you can help us guys with this problem, because we're running on a production server and clients are waiting to see their reports from the datawarehouse.

Thanx in advance


migration rejected from Jun 17 '13 at 18:14

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as too localized by Mark Storey-Smith, RolandoMySQLDBA, Jon Seigel, dezso, Max Vernon Jun 17 '13 at 18:14

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

done, same results (execution plan) – Hamzad_cne Apr 15 '13 at 10:37
same service pack and cumulative updates? – Mitch Wheat Apr 15 '13 at 10:38
slow server has more recent updates – Hamzad_cne Apr 15 '13 at 10:40
identical hardware? – Mitch Wheat Apr 15 '13 at 10:40
Please post the query and the 2 query plans. – Mitch Wheat Apr 15 '13 at 23:38