The anatomy of a process update – Part 2
In part 1 of this 2 part post I shared an overview of the procedure to optimise process update. I will now walk through the optimisation process on one of our multi terabyte systems.
Part 1 shows a visualisation of process update and i have captured timings in profiler for all the stages shown and I now present my starting point for tuning below.
We have 3 stages that can be optimised.
- Dimension build – This comes in at 92 seconds which is acceptable so i won’t be discussing this further but you can refer to part 1 for tuning options.
- Partition index processing – Almost 25 minutes is spent here so tuning is required.
- Commits – Almost 7 agonising minutes are spent waiting for the phase 2 commit with the server lock in place.
The Anatomy of a process update – Part 1
I have been working on optimising the process update of one of our multi terabyte systems and this 2 part post will break down the optimisation process and share with you some very interesting results.
In part 1 I will share an overview of the procedure to optimise process update and in part 2 I will apply this to our system and share some startling discoveries.
Overview
Internally process update is a complex operation and can be difficult to describe so to facilitate explanation I have created the visualisation shown below of the process which also helps to show the single threaded and multithreaded execution paths.
SSAS Maestro and the first challenge
Its official, 2012 ended on a high for me, I received confirmation that I have passed the Microsoft SSAS Maestro certification!!! I won’t repeat the details of the what the Maestro certification is but you can find details here and here.
Before I go on, I want to stop and say I loved the whole Maestro experience, i found it inspirational and invigorating! Thank you for the opportunity.
Now, what is the challenge i`m referring to? Well, I announced to the team of highly skilled BI developers I regularly work with that I was now a Maestro so they set me my first Maestro task to further optimise their already highly optimised process update and the results are really really interesting and I will be posting a 1 or 2 part blog entitled “The Anatomy of a process update” which will be coming shortly so stay tuned!
As a teaser, during the process of pulling apart process update some of the moments of discovery I had went like this.
“That explains why x appeared to yield no benefit….”
“Really!?!?! I never saw that coming”
“Wow! That’s one for the blog!”
“Maestro Moment — Realising the true potential and implications of the discovery…”
Stay tuned!
SSAS Free drive space command
Today i was reviewing some traces and saw 2 commands i had never noticed before which had been issued by SSMS, these are shown below.
SystemGetLogicalDrives
SystemGetAllowedFolders
The commands can be executed in a MDX query session.
SystemGetLogicalDrives returns the free space in MB for the disk drives that are contained within the server property “AllowedBrowsingFolders”, see below for an example.
I have checked and the commands work in all versions of SSAS back to 2005 and anyone can run them.
Personally i think this is really useful and here a few reasons why.
- Cut sysadmins out of the loop when maintaining space (this is a good thing
). - If your managing a remote dedicated SSAS server and have no access to the OS its a quick way to check space just like SQL Servers xp_fixeddrives & fn_fixeddrives()
- You might use it when collecting space stats or alerting.
- SystemGetAllowedFolders gives you the directories configured in “AllowedBrowsingFolders”.
- I hope you find this useful! I passed the info onto one of the Dev teams i work with and they certainly will make good use of it.
Windows 2008 file transfer performance improvements
I have been reading about Server Message Block (SMB) 3 and thought to myself it would be good to remind people about SMB 2 as many systems have not upgraded to Windows 2008 to take advantage of it.
For more background on SMB, please see this article.
When Microsoft introduced SMB 2 they pretty much started again. During one of my recent migrations I found that the file copy improvements from Windows 2008 R2 have literally been jaw dropping and the highlights are. Read more…
SSAS Multithreaded sync with Windows 2008 R2
We have been happily running some of our systems on Wndows 2003 and have had an upgrade to W2K8 R2 on the list for quite some time. The upgrade has now completed and we can start taking advantage of some of the new features which is the reason for this post.
For a long time we have used the sample Robocopy script from the SQLCat team to synchronize some of our larger SSAS databases. If your wondering what i mean by large, around 5 TB with a good few thousand partitions.
The script works like a dream but at this scale you soon find that the copy process starts to chug when it hits the fact data as this fantastic multi threaded script now becomes single threaded again as the bulk of the data being transferred is fact data.
SQL Server 2008 uses half the CPU’s
I recently got my hands on a couple of 4 socket servers with Intel E7-4870′s (10 cores per cpu) and with hyper threading enabled that gave me 80 logical CPU’s.
The server has Windows 2008 R2 SP1 along with SQL 2008 (Currently we can not deploy SQL 2008 R2 for the application being hosted).
When SQL Server started I noticed only 2 NUMA nodes were configured and 40 logical cores where there should have been 4 NUMA nodes and 80 logical cores (see below).
