Database Reference
In-Depth Information
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 evid-
ence 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 the box in the lower-right corner of the dialog labelled
Show all events
.
7. In the
Events
list, uncheck every box in the leftmost
Events
column. Scroll all
the way to the bottom and make sure every event is deselected.
8. Scroll back to the top and check the leftmost box in the
Events
column to in-
clude the following events in the final set of objects:
•
Query Begin
•
Query End
•
DAX Query Plan
•
Calculate Non Empty End
•
VertiPaq SE Query End
•
Serialize Results End
Search WWH ::
Custom Search