I had some sales reports that were running poorly on SQL 2005 Analysis Services. The information on the reports was mostly the same, it listed customers and the items they bought, or just items sold. The measures on the reports were all calculations. For a given date I needed to pull the quantity sold and dollars for the month searched on, year-to-date, same period last year, and all last year. The report included seven parameters besides the date. When you didn’t filter by anything this report would never complete. The RAM allocation would climb until the server had no memory left and then it would drop down to a few GB then start over climbing again. This process would repeat until the report timed out.

I had already tuned the cube design and calculations a fair bit and couldn’t see where to go from there. Then I setup a virtual server and installed SQL 2012 on it. I migrated the reports over to the new SQL 2012 report server but the performance was the same. And the memory allocation problem still occurred on the 2005 server where the AS instance was. Then I moved the AS database over to the SQL 2012 instance and ran the report. Without all of the parameters, the report finishes in less than 1 second. With the report parameters added in the report takes longer but still finishes in 5 seconds or less. I repeated this process on Enterprise Edition and BI edition with the same results. This was the easiest case for upgrading that I ever had to make. I’m loving SQL 2012 right about now.