When is SELECT INTO not minimally logged?
If you had asked me that question a month ago i would have said “Never, it is always minimally logged and this behaviour is documented in BOL’. But now I know better…
Recently i have been working on an application migration from SQL 2008 R2 –> SQL 2014 and we found a huge drop in performance for some SELECT INTO statements. I traced the issue down to the fact the operation was being fully logged and then we engaged Microsoft to work out why. Read more…
Automating process monitor data collection
Recently I needed to investigate why SSAS synchronisation commands were intermittently failing and the errors pointed to a file lock on a SSAS data file. To identify the file lock I needed to run process monitor for as long as it takes to capture the problem even if it takes weeks or months for it to occur. This presents the following problems
- How to prevent the process monitor capture from consuming all available disk space.
- How to ensure we do not over write the data we are looking for.
- How to ensure the capture stops once we have the data we are looking for.
Exposing the SSAS file system with the Metadata Sniffer
Very recently I encountered a production issue and to assist me in the root cause analysis I needed information/clues that are not exposed by any SSAS interfaces so I created the metadata sniffer.
Targeting CHECKDB SQL Agent jobs for resource governing
Some time ago I was looking into the performance of DBCC CHECKDB on one of our servers and came to the conclusion that the best Degree of Parallelism (DOP) for CHECKDB was not the same as our server level setting and since CHECKDB does not accept a DOP hint the only option to limit DOP when the server level setting is not appropriate is to use the resource governor.
In our environment CHECKDB is scheduled using SQL Agent and I could have chosen to simply target all SQL Agent jobs and have them all assigned to a single pool but this was not appropriate for all my SQL Agent jobs so I needed a solution targeting individual jobs. I thought this would be a relatively straight forward task but there were some challenges so i wanted to share them and my solution.
SSAS – Out of memory exception during processing
For a while now I been analysing a situation and i have got it down to a fine art now where by I do the following
- Create a memory a memory intensive situation where i am using 85% of available memory and SSAS is working hard to free memory.
- Process add a dimension and capture statistics.
- Review statistics, contemplate the results and then tweak something.
- Start at step 1 again.
The other day when i did my process add I was presented with the following error.
The following exception occurred while an operation was being performed on a data source view: The type initializer for ‘System.Xml.Schema.XmlSchemaComplexType’ threw an exception.;Exception of type ‘System.OutOfMemoryException’
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).
Working with SSAS traces and the dreaded Textdata in SSMS
For good or bad i regularly work with SSAS traces that i have saved to a table and many years ago i was regularly frustrated by the fact that the contents of the textdata column containing the query is always truncated at 65535 characters.
You may think 65535 characters should cover most queries but once Excel gets to work it generates some huge queries!
Fortunately i came across a solution by MVP Adam Machanic that uses a very nifty trick to convert the output to XML and this has the benefit that XML column data is presented as hyperlinks in grid view!
SSAS Crash Dump behavior
In SSAS 2005, when an exception occurs, you will always get a crash dump generated in the log directory of the instance and this could potentially lead to a lot of crash dumps consuming resources and a lot of work for the person who has to analyse the contents of each dump. One of the benefits of this approach is that if a user reported a serious error a quick check of the logs directory was a good indicator that there might be a problem.
In SSAS 2008 this behaviour changes and by default SSAS will generate one unique dump and then suppress all instances of duplicates until the service is restarted although the number of duplicates to be generated can be controlled by <MaxDuplicateDumps>
This is a good enhancement and it reduces the need to analyse a dump to confirm its a duplicate as well as introducing a mechanism to reduce the resource impact of dumps but its not perfect.
The biggest problem i have with this implementation is that there is no logging anywhere that a dump has been supressed.
In an ideal world there would only be one exception occurring in your system at any one time and monitoring would always catch that first exception but this is not an ideal world and IMHO there is no substitute for good logging.
I have raised a connect item to improve the crash dump behaviour and made the suggestions below. If you think the enhancements should be made then vote now.
-
When duplicate occurs log to event log dump signature, location & filename of last known dump generated, total duplicates to date.
- Introduce a mechanism to reset duplicate dump without restarting service.
- If an exception leads to a service failure a dump should always be generated
- Consider writing a stub to the log directory.
Extreme load and the “operation has been cancelled”
There are many reasons why SSAS may generate an operation has been cancelled but i want to take you through a new, although rare, one that i recently discovered.
To set the scene I had been tuning the Process Index phase on one of our servers so we could fit a full reindex of a 1.8TB cube comfortably into a maintenance slot over the weekend. I will write another post about the actual performance and how we achieved it.
My tuning was going very well and we were pushing the box to its limits which is exactly what you want to do in a maintenance window 
. I was running the index build server side and suddenly i received “The operation has been cancelled” message. I immediately went to look at my server trace and found that it had stopped recording at exactly the same time, how annoying.
With the current index build configuration every time i ran the build it failed with a cancelled error yet if i changed the configuration and eased of the throttle it would complete. This was unacceptable as i wanted every last drop out of the server. It was clear to me “something” was causing the cancel when i pushed the servers CPU’s to 100% so i dug out my friend process monitor and below is a screen shot of my “moment of discovery”.
Two new Analysis Services Blogs
Please welcome two new bloggers Pete Adshead and Christian Bracchi to the blogosphere!
These guys truly know what it means to build enterprise class highly scalable Analysis Services Solutions and you now how the opportunity to read about their experiences.
Don’t miss out!
SSAS 2008 R2 Operations Guide
What can i say but WOO HOO! The SSAS 2008 R2 Operations Guide has been published so download your copy now!
This document should be considered essential reading and contains over 100 pages of high quality information for configuring, testing and operating an Analysis Services server.
Finally i consider this document so important i have made a rare exception and gone back and added it to my Deployment Essentials post.
SQL Agent and SSAS Commands
SQL Agent can be used to execute SSAS commands such as processing of a dimension and i can see that it is useful when you want to quickly schedule an adhoc processing task but there is a major drawback you need to be aware of.
You are not able to specify the database to connect to and whilst you may think that it does not matter because the database is specified in the connection but it really does matter. Below are extracts of a processing task from profiler which was executed using a SQLAgent task.
So, what is happening here? Read more…
Parallelism, CPU Time & DMV’s
Whilst reviewing the CPU statistics of a system that i knew was CPU bound i found the numbers were not adding up and i was not seeing the code i expected to see as a top CPU consumer so i decided to going digging.
I quickly identified that if the query has gone parallel it:
- Only shows as one thread in sys.dm_exec_requests because sys.dm_exec_requests does not show blocking tasks and parallel threads appear self blocking. If you want to see all active threads including blocked you should use sys.dm_os_waiting_tasks but there is no cpu time there….
- Any cpu time shown is only relevant to the coordinator thread not the sum of all the related parallel threads.
I also used my favourite tool sp_whoisactive written by Adam Machanic but it did not help me either so i e-mailed Adam and had an enlightening mail exchange. The bottom line was that it is not possible to get an accurate value for CPU if a query has gone parallel! Below is an extract from the mail exchange with Adam reproduced with his permission.
Correct. It is not possible to get an accurate value for CPU if a query has gone parallel. The best you can get is the number of context switches (which Who is Active provides in @get_task_info = 2 mode). This is not the same thing, but it is correlated: a context switch means that the process was using CPU time, and was switched off of the CPU. What you don’t know is whether it used its entire quantum each time, or only 1/2 of the quantum, or whatever. So it’s not exactly a simple translation. But it’s a lot better than nothing.
Adam did continue on to talk about a potential method to expose a more accurate cpu value through Who is Active’s delta mode and shortly after he delivered! 
Adam announced the accurate CPU time deltas in this post. To get the CPU delta’s you need to be running version 11 and the parameter you need are documented here.
So, to summarise
- It is impossible to get a run time cumulative value of CPU for a spid that has gone parallel and it is vital you remember this when your looking at your DMV’s otherwise you could be barking up the wrong tree.
- sp_whoisactive can give you a runtime delta of CPU time for a parallel query which will enable you to spot CPU sapping parallel queries.
I hope you find this information useful and i would also like to say a big thank you to Adam Machanic.
SSAS Exposes a W2K3 bug
I recently came across a bug in Windows 2003 SP2 which causes a blue screen of death and SSAS was exposing the bug. In my scenario SSAS was the match, a file system cache function was the fuse and a BSOD the payload….. If your an all Windows 2008 shop then there is no need to read on and i am envious 
One of the configurations i always set on a SSAS server is LimitFileSystemCache and one reason is that if you do not it can grow to the point where it has a negative impact on SSAS. When you set this configuration SSAS will use the API functions “GetSystemFileCacheSize” and “SetSystemFileCacheSize” and this is where the problem lies.
When SSAS starts for the first time it will set the file system cache and your server will be fine until you have to stop and then start the SSAS service without rebooting the server. The problem lies in the fact that when SSAS stops the operating system cleans up the memory in relation to the file system cache and a structure is not reset and as soon as its accessed it causes the blue screen. The article and hotfix for this problem is KB979128.
So, if you have PreAllocated your SSAS memory which is something i often do then your quite likely to get the blue screen when SSAS starts but you might not as its very hit and miss. I found if i did not get it at startup i would definitely get it when i pushed the server to its limits.
Now should you rush out and apply this patch? Well, its always good to ensure stability but i have been supporting SSAS servers for years and the first time i saw the blue screen was a few months ago in test so you can at least take your time to test the patch before rolling it out…… 
Its also worth noting that other users of tools that set the file system cache may well experience this problem and i mention this because its not uncommon to limit the file system cache for SQL Database deployments…..
Below is the OS bug check we got when we hit the problem.
Reason Code: 0x805000f
Bugcheck String: 0x0000000a (0x0000000000000028, 0x0000000000000002, 0x0000000000000000, 0xfffff800010e9a8b)
Andrew Calvett's SQL Server Blog 