SSAS Locks - The daily processing takes too long

Question

Hi all,

I have a cube with 10 measure groups and 30 dimensions. Some facts have more than 100 million records and processing takes a long time. When I run the processfull manually I see that some queries slow down very fast to 5k records/sec. 

So I was wondering what is happening during the processing and by some search at Google I got the idea that there could be a few SSAS locks (there can't be any SQL Server locks as the ETL import is done before the SSAS process got started).

I have found out the locks during a processfull and I have 125 of them. 14 with locktype 2, 109 with locktype 4 and 2 with locktype 8. Now I have learned that 4 is a Write for processing lock which could slow down the whole process.

Now I really don't know where to start in order to solve this problem. Could you guys give me any pointers?

Regards,

Daniel

---

-- Information is king!

Answer 1

Hi,

It seems to me that you have a rather big cube and that the processing of fact tables is what takes the main time? Are you using partions for the fact tables? Your attribute relations are clearly defined in natural hierarchies? Have you tried to run process data first and the process index, instead of a full process of the partitions?

What kind of hardware are you using(number of CPUs, memory, disk systems)? What version of SSAS are you using?

BR

Thomas Ivarsson

Answer 2

Dear Thomas,

I am using partitions for my fact tables. 

The hardware is a two quad core with hyperthreading system with 64 GB ram and a Direct Attached Storage system of DELL.

I haven't tried to process the data first and later on the index. I am trying to do that now.

Furthermore, I see a lot of locks on various tables. Do you have any pointers to fix that?

Regards,

Daniel

---

-- Information is king!

Answer 3

Hi,

Llocks should only be a problem if you have users running queries at the same time as you are processing the cube. Processing aquires locks as well. 

HTH

Thomas Ivarsson

Answer 4

The locks in SSAS are highly unlikely to be causing you any performance issues. They are taken at a really high level and are there to prevent two users from independently trying to process the same object at the same time in two different transactions. It's more likely that you are having something like resource contention issues. The first thing I would be checking is your disk IO. You need to figure out if the issue is in the speed with which SQL is sending the data or if the issue is how fast SSAS can write the data. 

---

http://darren.gosbell.com - please mark correct answers