Extreme load and the “operation has been cancelled”

September 18, 2011 Leave a comment

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 Smile. 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”.

image

Read more…

Advertisements

Two new Analysis Services Blogs

September 18, 2011 Leave a comment

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!

Categories: Analysis Services, Tips

SSAS 2008 R2 Operations Guide

June 2, 2011 Leave a comment

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.

Categories: Analysis Services, Tips

SQL Agent and SSAS Commands

May 30, 2011 Leave a comment

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.

image

So, what is happening here? Read more…

Parallelism, CPU Time & DMV’s

May 5, 2011 1 comment

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! Smile

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.

Categories: DMV, Performance, SQL Server

SSAS Exposes a W2K3 bug

April 6, 2011 Leave a comment

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 Winking smile

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…… Smile

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)

SSAS Deployment essentials

March 30, 2011 Leave a comment

Over the recent years i have enjoyed the privilege of working on a number of different SSAS deployments. Some are huge, some are complex and some of them are huge and complex and most interestingly they all behave differently.

What i want to share today is what i consider to be essential for an SSAS installation. This covers what i expect to see installed to compliment SSAS, configuration settings to be changed and the literature that should be at your finger tips.

Read more…