Andrew Calvett

Working with SSAS traces and the dreaded Textdata in SSMS

2012-02-04T22:06:01Z

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...(read more)

SSAS Crash Dump behavior

2012-01-17T22:07:19Z

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...(read more)

Extreme load and “the operation has been cancelled”

2011-09-18T22:03:00Z

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...(read more)

Two new Analysis Services Blogs

2011-09-18T19:12:22Z

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!...(read more)

SSAS 2008 R2 Operations Guide

2011-06-02T13:59:00Z

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....(read more)

SQL Agent and SSAS Commands

2011-05-30T14:27:00Z

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...(read more)

Parallelism, CPU Time & DMV’s

2011-05-30T11:04:00Z

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...(read more)

SSAS Exposes a W2K3 bug

2011-04-06T22:05:00Z

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...(read more)

SSAS Deployment essentials

2011-03-29T23:50:00Z

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...(read more)

SSAS 2008 R2– Little Gems

2010-12-03T23:48:00Z

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...(read more)