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