SQL Server Profiler vs Extended Events for Analysis Services (SSAS) 2016 Database Administration.3/6/2017 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 
1 Comment
|
|||||
| Microsoft Data & AI | Database administration |
RSS Feed
