Enter the SSAS server level lock......

Ok, so your reaction to the title is probably the same as mine when i found out about SSAS server level locks! So, i will give you the scripts to reproduce the server level lock but first lets get down to business….  :)

Server locks were introduced in one of the SP2 cumulative updates. At the moment i can say it was pre CU12. I`m not sure why it was introduced but it likely to be in response to a “feature” :)

Fortunately the lock only appears at the end of processing when SSAS commits its data and commits are usually quick so depending when you do your processing you might never see it. So why am i so horrified by the existence of this lock other than its simply wrong to prevent connections to the server? Below are my concerns….

• If a query is running when processing comes to commit it must queue behind the query for a default of 30 seconds but processing still gets the server level lock granted meaning no one gets to connect for up to 30 seconds + commit time and users get connection errors!
• ForceCommitTimeout is the setting that controls the duration a commit job waits to kill the queries ahead of it. People should now think of this setting not only as the time your allowing queries to complete before being killed but also the additional duration of time your prepared to deny users access to the server.
• The real kick in the pants comes along when you find out that there are scenarios where a query will not respond to the query cancel invoked by ForceCommitTimeout. The obvious one is when there is a bug but there are others. This means that the commit can’t kill the query and your server is effectively hung and the users are screaming. What’s worse is the SYSADMIN can’t connect to the server to diagnose the problem because the server lock blocks them!
• I have seen connections error when connecting to the server due to the server level lock which is even worse. Unfortunately i have not managed to identify the repo (yet).

It is possible to reduce the impact of the server level lock and ways to do this are listed below.

1. Think about what your business will let you set ForceCommitTimeout to and bring it down if you can.
2. There is another setting called CommitTimeout which defaults to 0. If processing is unable to commit within this time it will rollback processing. Not ideal but its worth considering setting because it will get you out of the hole where your unable to connect to the server but make sure your fairly generous and don’t set it less than ForceCommitTimeout!
3. Worth mentioning you can set the CommitTimeout for each processing job you submit rather than at the server level.
4. If your fortunate enough that you never process when users are running queries then you don't need to worry.
   

Right then, you have made it this far and now you want to reproduce the server level lock and see it for yourself. Follow the steps below.

• Run the script below in SSMS in an MDX session (I lifted the script from Darren Gosbell).
• You need to amend the database name in the script. Don’t worry that its a database level read lock, all queries takes this lock so its a fair repo.
• Now process something in your cube and wait for it to appear to hang. From here on all your connection attempts will hang as they are blocked by the server level lock…
• If you connect up prior to firing the processing of, you can monitor the locks by executing an XMLA discover_locks command or by using activity viewer. Note that if your connected you can still run queries against cubes in other databases.

MDX SCRIPT

BEGIN TRAN

GO

<Lock xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ID>496CEC1F-D66A-4C80-9E14-305D6CF41D86</ID>
   <Object>
     <DatabaseID>Adventure Works DW</DatabaseID>
   </Object>
   <Mode>CommitShared</Mode>
</Lock>

What does a server level lock look like?

So, when you view the lock information a normal lock has an object element as shown below

<Object xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"><DatabaseID>Adventure Works DW</DatabaseID><DimensionID>MY Dimension</DimensionID></Object>

But the server level locks do not have any elements as shown below.

<Object xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" />

I hope you find this information useful!