Learning the Tricks of the Trade - Microsoft SQL Server 2008 R2 Administration - page 416

Similarly, if the SSAS client wants to connect to a named instance of Analysis Services (AS)
that is listening on another port number, then the client either has to specify the exact port
being used by the named instance, or it is directed to the Analysis Services redirector server
on port 2382 . The AS redirector service is part of the SQL Server Browser, which maintains a
record of the port number for each instance of SSAS and SQL Server services on that server.
Managing events capture to troubleshoot
problems
Performance slowdown is a common occurrence in any RDBMS platform and SQL Server
is not an exception. The root cause can range from inadequate database design that is,
improper configuration, to handling the application workload. The DBA must be able to identify
the root cause in a pro-active manner to minimize the problem. Also, the essential task is
to monitor when a simple query execution causes intermittent issues and take corrective
measures to fix the problem. The Windows operating system and SQL Server have a flurry
of tools and utilities to monitor the system activity and collect the required data, such as
PERFMON (SYSMON), SQL Server Profiler, DBCC statements, Dynamic Management Views
(DMVs), and Data Collector. From SQL Server 2008 version onwards, another diagnostic tool
is added, which is SQL Server Extended Events (XEvents).
In this recipe, we will go through the methods on how to manage events captured in SQL
Server troubleshooting scenarios, extended events with ETW-Event Tracing Windows.
Extended Events are designed to configure, monitor, and capture different types of data (CPU,
Memory, and Deadlocks), which includes performance data. ETW has been a built-in utility
since Windows 2000 that enables the tracking of kernel and application events with minimal
overhead. All the XEvents data collection processes are less-intrusive and provide a granular
level of information for a wide range of monitoring and troubleshooting performance issues. It
is helpful to take pro-active action when the server is experiencing severe performance issues
causing client applications to timeout.
How to do it...
The following are the steps to go through the methods on how to manage events capture
using Extended Events with ETW and TRACERPT tools:
1. FromStart | All Programs | Accessories | right-click on the Command Prompt and
choose the Run As Administrator to open a command prompt window to open a
dedicated administrator console connection:
SQLCMD -A