Skip to content

introduction-to-monitoring-analysis-services-with-sql-server-profiler.md

Latest commit

 

History

History
59 lines (41 loc) · 4.2 KB

introduction-to-monitoring-analysis-services-with-sql-server-profiler.md

File metadata and controls

59 lines (41 loc) · 4.2 KB
title Introduction to Monitoring Analysis Services with SQL Server Profiler | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology analysis-services
ms.topic conceptual
helpviewer_keywords
SQL Server Profiler, Analysis Services
monitoring Analysis Services [SQL Server]
performance [Analysis Services]
performance [Analysis Services], SQL Server Profiler
Profiler [SQL Server Profiler], Analysis Services
ms.assetid 568ec549-5ddc-493a-b9f8-3bdc548b562e
author minewiskan
ms.author owend
manager craigg

Introduction to Monitoring Analysis Services with SQL Server Profiler

You can use [!INCLUDEssSqlProfiler] to monitor events generated by an instance of [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssASnoversion]. By using [!INCLUDEssSqlProfiler], you can do the following:

  • Monitor the performance of an instance of [!INCLUDEssASnoversion].

  • Debug Multidimensional Expressions (MDX) statements.

  • Identify MDX statements that run slowly.

  • Test MDX statements in the development phase of a project by stepping through statements to confirm that the code works as expected.

  • Troubleshoot problems in [!INCLUDEssASnoversion] by capturing events on a production system and replaying them on a test system. This approach is useful for testing or debugging purposes and lets users continue to use the production system without interference.

  • Audit and review activity that occurred on an instance of [!INCLUDEssASnoversion]. A security administrator can review any one of the audited events. This includes the success or failure of a login try and the success or failure of permissions in accessing statements and objects.

  • Display data about the captured events to the screen, or capture and save data about each event to a file or [!INCLUDEssNoVersion] table for future analysis or playback. When you replay data, you can rerun the saved events as they originally occurred, either in real time or step by step.

Using SQL Server Profiler

To use [!INCLUDEssSqlProfiler] to create or replay traces, you must be a member of the [!INCLUDEssASnoversion] server role. If you are a member of the [!INCLUDEssASnoversion] server role, you can start [!INCLUDEssSqlProfiler] from the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] program group on the Start menu.

When you use [!INCLUDEssSqlProfiler], note the following:

  • Trace definitions are stored with the [!INCLUDEssASnoversion] database by using the CREATE statement.

  • Multiple traces can be running at the same time.

  • Multiple connections can receive events from the same trace.

  • A trace can continue when [!INCLUDEssASnoversion] stops and restarts.

    [!NOTE]
    Passwords are not revealed in trace events, but are replaced by ****** in the event.

For optimal performance, use [!INCLUDEssSqlProfiler] to monitor only those events in which you are most interested. Monitoring too many events adds overhead and can cause the trace file or table to grow very large, especially when you monitor over a long period of time. In addition, use filtering to limit the amount of data that is collected and to prevent traces from becoming too large.

See Also

Analysis Services Trace Events
Create Profiler Traces for Replay (Analysis Services)