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!

So, to demonstrate this in action below is a short code snippet to pull data from a trace i saved to a table and display the textdata as hyperlinks..

SELECT 
    (select [processing-instruction(q)]=TextData
        for xml path(''),type) As Query, 
    StartTime,
    EndTime,
    CPUTime,
    Duration
FROM 
    dbo.My_Trace_Table
Where EventClass = 10

The output is shown below

As you can see all the queries are presented as hyperlinks and you can read what you can in the cell or click the link for another surprise.

The surprise is that Adam Machanic uses the processing-instruction() xpath function which bypasses encoding so characters like > are not changed to &gt and all the formatting is preserved. So, if the client submitted a formatted query that will be preserved in the link. Below is an example output from one of the links i clicked.

The eagle eyed amongst you will notice the <?q & ?> at the beginning and end, this is a small price to pay.

If your unfortunate and find the query is not formatted and you just get one long string then i always turn to the trusty online MDX formatter here by Mosha Pasumansky.

Finally, you may ask yourself if there is a limit to the data size and the answer is its configurable. By default XML data is restricted to 2mb but you can set it higher if required.

I have been using this technique for a good few years now and have used it to solve many different challenges however it was only recently that i was reminded that this may not be a widely known solution so i hope you find this as useful as i have.

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.

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

So, what’s happening?

• Above are details of the TCP communications occurring between SSAS, ASTrace (my tracing tool of choice) and SSMS.
• The numbers in the paths are the ports being communicated on and i have highlighted this so you can see SSAS communicating between SSMS & ASTrace
• Suddenly there is a block of TCP Retransmits and SSAS disconnects the clients 4 seconds after the first retransmit! This coincides precisely with my “Operation has been cancelled”.

What i also noticed was that when these retransmits occurred the processors had just gone from 99% to 100% so it seems that i may have been exposing a “weakness” in the tcp stack when under very heavy load.

My next step was to see if the retransmit is configurable, which it is. To increase the retransmit period you change the TCPMaxDataRetransmission value in the registry as documented here.

On our servers the retransmit was set slightly below the default value so I increased it to slightly above and my process indexes went through.

Below is the output from process monitor with the registry change in place. As you can see there are more retransmits but this time it does not end in disconnection.

Now, you may be concerned that this slows down the overall processing but in my case this patch of retransmissions happened once or twice during the processing so did not cause a material increase in processing time.

If you are experiencing the same symptoms and need to change the registry value you must also consider the impact of changing on clients and remember that i have only seen this when the server is under extreme load un a Windows 2003 SP2 box.

Finally, to make this discovery an even sweeter victory, once the index builds were going through i was able to shave another 10% off the run time.

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!

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 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 it really does matter. Below are extracts of a processing tasks from profiler which was executed using a SQLAgent task.

So, what is happening here?

1. The processing command starts and a processing schedule is built but no database name is specified because its missing from the connection string.
2. The XMLA command specified the ResourceDB was to be processed so SSAS now switches to this database and starts the processing steps.
3. This is the real gotcha. At point of commit because no database was specified SSAS switches to the default database. If there is processing occurring on the default database processing on the ResourceDB is blocked and you might also find yourself suffering at the hands of a server lock. Depending on your ForceCommit Timeout and Commit Timeout settings this processing could terminate the other processing, rollback or worse if your really unlucky.

So, what is the SSAS default database? There are 2 ways to look at it. It is either the first database created on the instance or the oldest database on the instance (should you have deleted the first). Some of you may have noticed the commit subclass and thought you had not seen it before. It was first introduced in SSAS 2008 R2 and prior to R2 you had to a) Know there was such a thing as commit and b) Calculate it by taking the difference between the batch completed event and the one immediately before it.

In my opinion SQL Agent Analysis Services command is not a viable option for scheduling processing whilst this issue exists and i have raised a connect item for it.

In the mean time i have put together a very simple SSIS package which you can schedule via SQL Agent and you can specify the database name & commit timeout (so you can avoid impacting other processing). You also specify the path to the xmla file you want to run. Attached is the SSIS package and T-SQL to create the agent job, all you have to do is update the set values.

There are many other ways to run adhoc processing including ascmd and i hope you find this method quick & effective.

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.

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)

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.

Tools of the trade

ASTrace

I simply can’t imagine life without ASTrace. This utility writes a SSAS trace to a database and effectively turns a black box into an open book. The trace data is an absolute goldmine and with it i am able to work out why the users reported slowness 5 minutes ago and i have been able to get to the root cause of SSAS crashes quickly and work with Microsoft effectively. Other uses are to allow developers to run a stored procedure against the data to give details of whats currently running without granting sysadmin on the SSAS server. The opportunities are huge and if you don’t want to write a trace to a db then write it to a file but make sure you have a trace!

Resmon

This is a cube that captures resource information about SSAS and i love the irony of a cube that captures metrics on the health of SSAS! !  Its a fantastic tool and for example exposes details of your memory utilisation and you can drill down to the most granular levels. An example of what i might use it for is analysing performance of a cube on the server after a release by comparing before and after and perhaps identify a poorly designed attribute…... The potential is huge and the footprint is small so get it installed now from here. I would also encourage some community effort to enhance the cube, i have a number of ideas and know of other similar tools which could be merged together so watch that space for future enhancements.

SSAS Stored Procedure project

Another fantastic tool to extend functionality. Read what it can do (and share it with developers) and then decide if its for you. Some of the uses may not be immediately obvious but i like to have it on our non production servers as a minimum just to be able to clear the filesystemcaches easily so that testing is consistent and repeatable.

Activity Viewer

A must have tool to quickly see whats happening on the server. It compliments the traces nicely but i have found myself using it less with the introduction of DMV’s in 2008.

Essential Literature

SSAS 2008 R2 Operations Guide

IMHO everyone should have a copy of this on their desk and/or one click away and you should have read it more than once.... :D

Analysis Services Performance Guide

This is an absolute must read and one vital thing to remember is you don’t read it once, you read again and again and then you have at your finger tips for the next time you have to troubleshoot a box. This guide also covers a number of configurations settings and how changing one setting impacts another and yes this is where i read about MDX blocking.

Microsoft SQL Server 2008 Analysis Services Unleashed

IMHO (again?) YES! This book is fantastic and whilst it is technically deep its an absolute gold mine which has helped me get my head around and resolve mind numbing issues.

Vidas Matelis

Vidas Matelis recently put up a post that links to many great documents which he read in preparation for the SSAS Maestro. I would encourage reviewing the documents.

SSAS Wiki

Last but by no means least (especially as some of my post are present on the site ) i recently became aware of a wiki that someone has been working hard on to centralise SSAS information.

Configuration

There are only a handful of settings i change out of the box because to be honest Microsoft have done a damn good job of the default configuration and the other settings i change arise from monitoring the behaviour of  SSAS so the settings i am about to discuss are my baseline.

Note: Settings in a green or red indicate a dependancy

So, as you can see i do not change many configurations and i must stress this is what i consider to be the optimal starting point to baseline from. Its quite likely other settings will be tuned and you might even need to tune the ones i have highlighted but that is less likely.

I want to finish this post by pointing out the one thing that is often missing in an SSAS deployment and that is a strong relationship between the DBA’s and the developers. This is fundamental and if it does not exist you will pay the price. I maintain a strong relationship between DBA & Dev and i believe there is a lot of respect from both sides which under pins our successes.

I have spent the last few days working with SSAS 2008 R2 and noticed a few small enhancements which many people probably won’t notice but i will list them here and why they are important to me.

New profiler events

Commit: This is a new sub class event  for “progress report end”. This represents the elapsed time taken for the server to commit your data. It is important because for the duration of this event a server level lock will be in place blocking all incoming connections and causing time out. You can read about the server level lock here. Prior to 2008 R2 the only way to calculate the duration of the commit is to subtract the end time of the batch completion event from the event immediately preceeding it. You want to be looking out for this event if people are complaining about connectivity……

File Load and Save: This is a whole new category with its own events but you must remember to tick the boxes to show all the categories to see it though otherwise its hidden. Anyway as soon as i saw it i thought awesome! As the name describes it exposes the file activity and gives a whole new level of insight into the black box known as SSAS . You may be wondering how it is useful to know what files are being accessed but when your trying to understand whats going on under the hood and where a bottleneck might be its invaluable and i have been using it these past 2 days whilst working on a problem which i will discuss in a future post.

Vertipaq SE Query: Not much to say here other than i would expect them to be fast…..

Other enhancements

In august 2009 i posted about The cost of SSAS Metadata management and discussed a hidden performance penalty. Well i am pleased to say that Microsoft have fixed this particular problem and when you process a partition it will not check for dependencies across every partition in the database any more…… Now before you get excited and decide to create thousands of partitions and throw away your partition merging scripts you should wait for the post i allude to earlier in this post as there are other significant costs & penalties for having to much metadata………

Last but not least a big thank you to MS for improving the time it takes to expand an object in SSMS! With SSAS 2005 & 2008 i could be waiting 30 seconds to a couple of minutes to expand a node in SSMS which is very frustrating when your in a rush but with SSAS 2008 R2 node expansion is now instantaneous! So thank you again, it may be a small fix but its a big time saver!