• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

Database administration

SQL Server Profiler vs Extended Events for Analysis Services (SSAS) 2016 Database Administration.

3/6/2017

1 Comment

 
I recently taught a live webinar for Pragmatic Works on SSAS Monitoring and Performance Tuning with Extended Events.  It is worth noting that Profiler is still available to us in SQL Server 2016, and continues to provide benefits that Extended Events do not.  This is how I see it.

SQL Server Profiler 2016
1.  This very fine SQL Server tool is being deprecated by Microsoft in a later version as stated on MDSN here.  The good news is that Profiler is not deprecated quite yet.  Hooray!

2.  This is still the fastest way to log SSAS query activity for immediate analysis.  You do not need to write out to a file, view said file, and import.  SQL Profiler provides a very handy viewing tool that is real time.  When needing to complete a one-off monitoring of a problematic query, SSIS package or SSRS report, this is the low hanging fruit.

3.  Granted, Profiler has a heavier "footprint" than Extended Events (i.e. it takes more system resources). Coupled with the fact that it is going away ... eventually ... it should not be considered as a long-term monitoring tool of choice.  When I say "long term monitoring" I am referring to scheduling monitoring through a SSIS package.

4.  When working with Profiler and SSAS, it is important to Show All Properties on the Events Selection tab of the Trace Properties window and mark relevant SSAS events.  Not all SSAS events are marked by default when connecting to a SSAS instance and taking the default SSAS trace.  The list of tabular and multidimensional events I use most often is given below.

Extended Events (EE) in SQL Server 2016
​1.  It is sometimes misunderstood, but EE is not a new SQL 2016 SSAS feature.  You could use XMLA in earlier SQL Server versions.  Chris Webb has a very good BI Blog post here that talks about doing this very thing in SQL 2012.

2.  What is new in SQL 2016 is the user interface for creating SSAS EE sessions.  Unfortunately, I feel that Microsoft did a half-hearted effort in this, and that is the politically correct way of saying it.  In a SSAS instance, you can create EE sessions, but you cannot edit them.  For real?  The solution is to use the GUI to setup the session, then be sure to script it out before you save it.  

3.  When working with EE for SSAS, I recommend you use a XMLA query in SQL Server Management Studio (SSMS).  Attached to this post is the XMLA for creatings, stopping, deleting and querying current EE sessions for both tabular models and multidimensional cubes.

4.  If using EE to warm your SSAS multidimensional cube cache, or to monitor user query durations and execution counts, EE and SSIS is definitely the best solution.  Use a SSIS package on a scheduled job to automatically stop, import and restart EE sessions.  Use t-sql against the resulting SQL Server database table, and SSRS to automatically report long running queries.


Profile Trace / Extended Events SSAS Events:
This is not a complete listing, but these are the events that I find most helpful when monitoring SSAS.
Multidimensional
    ExecuteMDXScriptEnd
    GetDataFromAggregations
    GetDataFromCache
    ProgressReportEnd
    QueryCubeEnd
    QueryEnd
    QuerySubcube
    QuerySubcubeVerbose

Tabular
    DAXQueryPlan
    QueryCubeEnd
    QueryEnd
    QueryExceedsMemoryLimit
    VertiPaqSEQueryEnd (using the VertiPaq Scan subclass event)

XMLA File Downloads
dba_create_stop_delete_xevents_for_multid_cubes.xmla
File Size: 4 kb
File Type: xmla
Download File

dba_create_stop_delete_xevents_for_tabular_models.xmla
File Size: 2 kb
File Type: xmla
Download File

1 Comment

    Categories

    All
    Columnstore
    Connecting
    Date & Time
    DBA
    Extended Events
    Indexing
    Installation
    Oracle
    Performance
    SQL 2016
    SQL Developer
    SSAS
    SSIS
    SSRS
    T-SQL

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About