Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Usage-Based Optimization Wizard

Prepare the Server Environment for the Lesson

1. Start Analysis Manager.

2. Expand the Analysis Servers folder by clicking the “+” sign to its immediate left.

Our server(s) appear. (Mine appears as MOTHER1 in some of the illustrations in the article).

At this point, let’s modify the sampling frequency of the Query Log for the SSPerf_Warehouse cube, so as to allow it to capture enough meaningful data to proceed with the remainder of the article.

3. Right-click the Analysis Server.

4. Select Properties from the context menu that appears, as shown in Figure 1.

Figure 1: Select Properties for the Analysis Server


The Properties dialog opens, defaulted to the General tab.

5. Select the Logging tab in the Properties dialog.

6. Replace the default of “10” with a “1” in the Write to log once per box.

The Logging tab in the Properties dialog appears as depicted in Figure 2.

Figure 2: Logging Tab, Analysis Server Properties Dialog - Modified Setting Circled

7. Click OK to accept the setting change.

NOTE: For more details on the Query log and its settings, see Optimization Tools: Basic Usage Analysis.


The Properties dialog closes, whereupon we are greeted with a message box, as shown in Figure 3, warning us that we must stop / restart Analysis Server to make our setting change permanent.

Figure 3: We are Warned to Stop / Restart Analysis Services

8. Click OK.

The message box closes, returning us to Analysis Manager. We will now stop the server, and then restart it, to effect our change to the sampling frequency of the Query log.

9. Select File ---> Exit from the main menu.

Analysis Services closes.

10. Navigate to the Control Panel.

11. Double-click Administrative Tools within the Control Panel.

12. Double-click Services.

NOTE: Depending upon your operating system / environment, navigation to the Services window may differ.

13. Right-click the MSSQLServerOLAPService entry in the Services listing.

14. Select Stop from the context menu that appears, as depicted in Figure 4.

Figure 4: Stopping the Analysis Server

We obtain indication that MSSQLServerOLAPService is stopping.

15. Once the service is stopped, right-click the MSSQLServerOLAPService entry in the Services listing again.

16. Select Start from the context menu that appears.

MSSQLServerOLAPService restarts, as indicated in the Services window.

17. Select File ---> Exit to close Services.

We will now return to Analysis Manager.