(en)One of the features new in Analysis Services 2012 is the support of the SQL Server Extended Events Framework.

While there is no GUI support for that, yet, it is however possible to set up a XEvent session via DDL commands - just like it was in the “old days” with SQL Server 2008/ 2008 R2, until 2012 brought the GUI.

Since I have been asked a lot at my sessions on Extended Events on how it is done in Analysis Services, and the Books Online sample code is not really working (“Use SQL Server Extended Events (XEvents) to Monitor Analysis Services http://msdn.microsoft.com/en-us/library/gg492139.aspx”), I will show a quick example here.

The following code creates a session to collect the deadlocks events from the Analysis Services Instance:

As one can see, the definition like session configuration and targets, is quite similar to SQL Server, since it is in fact based on the same architecture.The package containing the events is "AS", whereas targets come from our well known "Package0". The one familiar with XEvents will know, what this implicates - more maybe in a later post.

Via the internal system view $system.discover_traces, we can see the active traces on the instance: the “FlightRecorder” which is still using the old-style Tracing technology (I wonder when Microsoft will add a new one just like system_health in SQL Server) and my sample session. You will also note, that the XEvent session’s trace file name is not visible here.

So as you see, for a fact, the Analysis Services engine has been extended to be using the Extended Events architecture for better performing and more flexible Tracing.I do expect some more GUI-support for Managing XEvents in Analysis Services in the next major release.

Have fun, playing around with the sample. :-)

From now on there is no excuse any more, to burden an Analysis Server that is already on its knees with Profiler...

Meanwhile I’d like to refer you to this article from another fellow MCM, Jonathan Kehayas, where you can see the enormous difference in terms of negative performance-impact of tracing via Profiler and SQL Trace vs XEvents:

(en)The Topic Locking and Read-Only for filegroups and databases is one of the ongoing myths around SQL Server in forums – and at least half of the information unfortunately wrong. Since I recently fell into the trap myself, I want to write down, how it really is.

To have a definite picture, I made 3 test series under 3 different isolation levels:

1. Read Committed

2. Read Uncommitted

2. Read Uncommitted

3. Repeatable Read

3. Repeatable Read

A note about Statistics und eXclusive Locks on ReadOnly-Databases:

Yes, one can indeed watch X-Locks on Read-Only databases. And this happens when auto-created stats jump in. This works only starting with SQL Server 2012. The statistics themselves are stored in Tempdb. In order to create those, SQL Server generates SCH_S and METADATA locks on several system objects (here: sysallocunits, sysschobjs, sysobjvalues, syssingleobjrefs, sysobjkeycrypts):

This is of course not the most common scenario, but it does happen (especially in AlwaysOn scenarios with read-only secondaries involved) and belongs to a complete picture.

-

Besides that one can see on first sight, that there is no diffference in the Locking behaviour beetween the table on a ReadWrite Filegroup (here Primary) and the table on the ReadOnly filegroup.

Only if the whole database is ReadOnly, SQL Server saves himself the Page- and Key- locks. Even there an Intent shared Lock is placed on the table.

Conclusion:

Putting Tables onto a ReadOnly-Filegroup does not save Locks. But it often does make a lot of sense, to break up databases in this manner. Just thinking of: less backup, faster restore, NTFS-compression etc.

From October 22nd – 25th I will hold several sessions on: ”AlwaysOn and ReadOnly Routing”, “Data Corruption Survival with CHECKDB”, “Security” and “Tracing with Extended Events” in the track ”SQL Server 2012 Toolbelt for DBA’s and Developer”in Seeheim, close to Darmstadt, Germany at the PASS Camp

(en)as some of you already may have heard: this June I passed my Lab Exam for the Microsoft Certified Master SQL Server 2008. - almost exactly 3 months after my first attempt. In fact the Exam has already taken place in the middle of May. - Unfortunately this time I had to wait more than 30 days until I received my result, and it took another couple of weeks, until my name made it onto the official MCM-List: http://www.microsoft.com/learning/en-us/mcm-certification.aspx

The MCM is the highest technical certification that Microsoft offers (http://www.microsoft.com/learning/en/us/certification/master-sql.aspx), and unlike the MCITP or even MVP the MCM is only awarded for a so called knowledge exam plus a practical lab exam of approximately 5 1/2 hours practical problem solving on a SQL Server box. In fact, as of September 2012, I am the only Microsoft Master for SQL Server in Germany outside of Microsoft and affiliates. :-)

Thanks to all the people that believed in me, and to those that made me pursue it even more determined :-) Among them: Paul Randall and Kimberley Tripp from SQLSkills - my number one teachers, in general terms, since several years, Jonathan Kehayias and Bob Beauchemin who added valuable knowledge in SQL OS and Developing, Robert Davis, who graded my first Lab within hours, and last but not least: Ralf Dietrich, my long term sparring partner for SQL Server and uncertified Master in Germany ;-)

Also I have to apologize to all my customers for often letting them wait long because I made my studies number one priority. It will be made up by even better and more effective consulting skills :-)