On a previous post I wrote about how the number of databases in an SSAS instance can make an impact on the overall performance of the instance. In this post I want to discuss another, more “explainable” property that you can set in order to improve cube processing performance.
Trying to optimize processing time of our databases I set up a performance monitor trace to identify if we are having any resource bottlenecks that we need to address. Looking at the “Physical Disk: Disk Writes Bytes/sec” counter I noticed that it recorded a fairly small numbers most of the times (between 1MB and 500KB of data written during a “dimension update” or partition processing” events). This is obviously way below the amount of data that can be written to disk (we use a very advance storage system that can cope with a much more intense disk write profile).
I also was looking at the SSAS counters “Threads:Processing pool busy threads”, “Threads: Processing pool idle threads”, “Threads:Processing pool job queue length” and “Threads:Processing pool job rate”. These counters show how the threads allocated by SSAS to perform the processing operations are behaving. I noticed that:
1) There was no job queue at all – which means all processing threads never waited – which is good.
2) The total number of threads (busy and idle) never exceeded 60.
In SSAS there is an (advanced) server property which tells the SSAS processing engine how many threads it can create for a processing operation, this is the Threadpool\Process\MaxThreads property. Its default value is 64 and that correlates with the number of threads I observed during processing. I increased this value to 128 and indeed the number of threads created during processing increased to approximately 120, but even better, I noticed that the disk write rate increased up to 15 MB/sec. There was still no job queue reading so I figured I have succeeded in this modification. Needless to say processing time decreased as well by 40%. I’ve increased this property to 256 but at this stage job queue was starting to increase from zero up to 15 and the number of threads created did not increase over 140 and I was not able to identify an increase in disk write rate as well. So, for me, 128 was the magic number for optimal processing performance.
If you are trying to optimize processing performance of your SSAS databases and are running your SSAS instance on a multiple CPU server, look to to the Threadpool\Process\MaxThreads property to see if this needs to be modified.
-Yaniv
(This is post number 21 for week number 25 of my SQL blog challenge)
I’ve been researching a few performance issues lately on an Analysis Services 2005 instance with 20 (almost) identical databases on it, each weighing roughly 2.5GB and containing 6-7 cubes with approximately 400 partitions in each cube. As you can imagine, this makes up quite an impressive number of files stored on disk. SSAS stores everything on files, and unlike its relational relative (no pun intended), which will store hundreds of GB of data on a fairly small number of large files (I know this is an over simplification of how one should plan for data file allocation on SQL Server but its good enough and true enough in most cases for the sake of this post), SSAS stores a single database as described above in hundreds and hundreds of files. The number of files required is determined by the number of partitions, dimensions, cubes and aggregations and since we have lots and lots of those elements we will eventually have to deal with a heavily congested file system structure.
We have identified that a similar instance, containing only 10 databases performs better in both processing time and query response time. One thing I should note is that on the instance with the 20 databases, only 10 are in use, the rest were there purely due to historical reasons (we actually moved those 10 idle databases to a new instance – the instance that now performs much better).
What we first had to make sure is that both instances are operating under the same server properties, the same disk system and have available to them identical resources such as CPU and Memory. Indeed all of these parameters were similar.
For each SSAS database there is a metadata overhead, this means that when databases are initialized, metadata on the database is loaded into a non-shrinkable section of the SSAS memory. If you have many databases, initialization will take longer. If these databases are complex (i.e. contain many dimensions and partitions), initialization may become a more serious issue. However, we did not experience memory issues on the machine that ran the 20 databases instance. Still, we figured that the fact the disk was loaded with files (1000’s of them), it is best to drop all unnecessary databases and see what happens. Once 10 databases were dropped, performance on this instance became equivalent to performance on the first 10 database instance.
I do not have a clear—cut answer as to why this was the silver bullet we were looking for. As said, the 10 databases that we dropped were not in use in any way – not for querying and certainly not for processing. Still, dropping them and leaving only 10 “live” databases proved to be the trigger that we were looking for to improve performance on this instance.
So ,there you have it: make sure you do not have too many databases on a single instance, especially not “complex” databases that contain many dimensions, partitions and aggregations.
BTW – if any of the readers can shed some light into this – please do so, it will be much appreciated!
-Yaniv
(This is post number 20 for week number 24 of my SQL blog challenge)
I was asking the readers to tell us how their SQL Server career started and I though I’d share my story with you as well: I have actually started working with databases when I was a student. I was working at Rambam Medical Center in Haifa in a part-time job as a developer, writing applications in Access and Delphi (?!) with an underlying Oracle database. When I completed my studies I got a job as a Project Manager at Panorama Software Systems. This company manages data warehouse projects and sells front-end business intelligence software based on the Microsoft platform (well, at least back then it was purely Microsoft oriented). So, on my first day at work I was given a desk, a chair and a desktop and I started learning SQL Server 7.0, from the ground up: database engine, analysis services, dts, the works! More than 10 years down the road – and I’m still working with the product, which have changed dramatically in this period as you may know.
Important note: I was given a clarification regarding the giveaway ticket for the BI seminar – The free ticket will grant the winner access to a single day of the seminar (and not the two days), so apologies for the confusion.
-Yaniv
(This is post number 19 for week number 23 of my SQL blog challenge)
Technorati Tags:
SQL Server