Performance Tuning and Troubleshooting Tabular Models - SQL Server Analysis Services 2012 Cube Development - page 404

Using performance tools
Your design and management tool belt will contain several tools that will help you with
troubleshooting, optimization, and performance tuning. These may consist of:
• SQL Server Management Studio
• DAX Studio add-in for Excel
• Windows Task Manager and Resource Monitor
• SQL Server Profiler
Task Manager and Resource Monitor
The most convenient method to check system resources and activity is that of using
performance tools in the Windows Task Manager. You can access the Task Manager
in any version of Windows using Ctrl + Alt + Delete or by right-clicking on the Win-
dows task bar. Use the Performance tab to view a summary of CPU and memory
usage on the computer. Memory use is typically the most important factor to consider
first. This will tell you how much RAM is available on the system and how much is cur-
rently in use by the Tabular engine and other processes. Use the Processes tab to
see whether any other services or applications are competing for memory. You should
typically be concerned when memory use is consistently over 80 percent or spiking
over 90 percent, but there are many other factors to consider. On a production server,
you should maintain a memory reserve to cover for multiple models being loaded into
the memory and have enough additional memory for database processing.
The CPU usage is also an important factor in performance. If all the CPU cores are
running high consistently when queries run, this could be an indication of inefficien-
cies in the model design or the calculation of formulas. Disk I/O and network per-
formance are usually less important in monitoring Tabular's query performance but
can be important considerations when processing the database, tables, or partitions.
Depending on your version of Windows, you can get a more extensive view of these
resources and processes using the button on this dialog to open Resource Monitor .
Dynamic Management Views
Several Dynamic Management Views ( DMVs ) were added to the SQL Server to
support Tabular Model's design and optimization. These views are queried using OLE