Home > Analysis Services, Configuration, Locking, Trouble Shooting > Enter the SSAS server level lock……

Enter the SSAS server level lock……

September 23, 2009 Leave a comment Go to comments

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 2005 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”&gt;
<ID>496CEC1F-D66A-4C80-9E14-305D6CF41D86</ID>

<Mode>CommitExclusive</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

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!

Advertisements
  1. Juergen
    March 17, 2016 at 16:19

    Hi, actually I’m faced with the Problem that new Connections are queued when the Sync Job is entering the syncphase (EventSubclass= 39 – Synchronize). So the Excel press connect -> Freeze,But when I use some Recent Connections to a different cube it works. So do u know is there an “Database Lock” which starts occuring when copying the files from source to Target “Temp”
    We already Trace the final Phase (Serverinstance wide locks), which takes about 1 Minute for our biggest cube. But the sync Phase is starting 20 min before, so we ran into an really Long lock for new Connections. I’m doing everything to convince the Business to load not at workingtime. BUt…
    Thanx
    Kr J

    • March 18, 2016 at 16:36

      Hi,

      There are database level locks but I have not experienced the problem you describe with the SSAS sync. The locking mechanism is only supposed to block writers up to the point where a commit starts where readers and writers can then be blocked. You mention excel so perhaps your users are creating session cubes which then might be incompatible.

      To identify if this is a locking problem you can use the DISCOVER_LOCKS DMV or you can capture lock information in your traces.

      Hope this helps!

      Andrew

  1. October 9, 2012 at 15:19
  2. October 9, 2012 at 15:20
  3. February 21, 2013 at 23:39
  4. March 18, 2017 at 23:06
  5. April 19, 2017 at 20:15

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: