Investigating query performance with SQL Server Profiler

The most important step in determining performance bottlenecks is to find the cause. You need to discover specific events that are taking too long to run or provide evidence of the issue. The most effective way to decompose a query and understand the individual tasks it performs is to run a trace using the SQL Server Profiler. Often times, this is simple but it can also be a tedious and time-consuming process, depending on the complexity of the issue.

How to do it…

Let's get started with running a Profiler trace:

1. You will open SQL Server Profiler and start a trace to capture events on the Analysis Services instance.
2. From the Microsoft SQL Server 2012 program group, expand Performance Tools and click on SQL Server Profiler.
3. Click on the top-left button on the toolbar to start a New Trace.
4. When prompted, connect to the Tabular instance of Analysis Services.
5. On the Trace Properties dialog, switch to the Events Selection tab.
6. Check...