Jack Li has passed along a great tip on how to improve the performance of building the Project Server Data Analysis Cube:
Recently we have worked with a big enterprise customer using Project Server. They ran into an issue where Project Server reporting component which uses Analysis Server ran very slow during cube population. Eventually, the issue was tracked down to a single query. The query would take 17 hours to finish for 1 year of data. nHowever, if one applied force order to the query, it would finish within minutes.
Customer had tight deadline and need a solution fast. It would be easy if one could just modify the query to add force order option. Unfortunately, the query was generated by Analysis Service based on the cube definition. In other words, it couldn’t be changed.
Enter into plan guide for the rescue. SQL Server 2005 gives you various means to influence the query plan for a query without adding hints to the query itself directly. You don’t need to change the query which may not be possible without changing the application.
The simplest one is the use use sp_create_plan_guide stored procedure to add a query hint like force order etc.
Here is exactly what we did for our customer:
1) We used profiler trace to trace the exact query
2) We copied the exact query text and use sp_create_plan_guide to create the plan guide:
EXEC sp_create_plan_guide N'guide_forceorder',
N'OPTION (force order)'
Steps are fairly easy yet it is tricky to implement. If you are not doing it correctly, SQL Server may not use the plan guide you just created. Here are a few things you need to watch for:
1) ensure you have the exact text. Even if you miss a space character, the plan won’t match. To ensure you get exact text, you should launch profiler trace while running the application to get the query. See http://msdn2.microsoft.com/en-us/library/ms188255.aspx for more details.
2) There are two ways you can verify if SQL actually uses your plan guide. The first one obviously is that your query finishes faster. Another way is that you will see PlanGuideDB word in the xml plan. So if you do set showplan_xml on and then run the query, you will get xml showplan. If you search the text, you will find PlanguideDB.
There are more advanced ways to use plan guide including parameterization or use plan. Please refer SQL Server 2005 books online for these advanced topics.