Hello Friends and Geeks,
I write this blog when the tallest Ganesh of Hyderabad is on the way for Immersion.
Coming to the topic for this post
Most of us know, how read/write concurrency and locking works with SQL Server Database Engine.
Ever wondered what would be the locking pattern for Processing and Querying concurrency for SQL Server Analysis Services (SSAS)
The following illustration would help understand how processing and locking works for SSAS
ForceCommitTimeout setting of SSAS controls the amount of time SSAS would wait for the commit to complete. The default value for this setting is 30 seconds. Under the default setting, SSAS waits for the MDX/DAX query to complete for 30 seconds and if it does not complete by this time, kills the query and proceeds with the Commit. This is the scenario where in reporting users would see an error message with locking conflicts. All the subsequent reads on the partition would go into a wait queue until the commit completes
Now, lets say that we want to give higher priority to end user queries and lower priority to Processing. In this case we would change ForceCommitTimeout property to 0 and CommitTimeout property to 30 seconds. The result of this would be SSAS Processing would wait for 30 seconds for acquiring the locks and in case it does not get the lock, gives up on processing with failure. Its important to build a retry mechanism in the Processing job in such cases (as processing failures would make the data stale in cube/tabular model and users would complain about data latency)
I hope you appreciate this very important concept of how SQL Server Analysis Services handles read-write concurrency
Happy Learning!!
Veera
Join the fastest growing SQL Server group on FaceBook
