Browse by Tags

Working with SSAS traces and the dreaded Textdata in SSMS
04 February 2012 22:06

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... Read More...

by ACALVETT | with no comments

Filed under: Tools, Tips, SSMS, SSAS 2005, Analysis Services, SSAS, 2008, SSAS 2008 R2, 2008 R2, SSAS 2008, Trouble Shooting, Profiling

SSAS Crash Dump behavior
17 January 2012 22:07

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... Read More...

by ACALVETT | with no comments

Filed under: Tips, SSAS 2005, Analysis Services, SSAS, 2008, SSAS 2008 R2, 2008 R2, SSAS 2008, Trouble Shooting

Extreme load and “the operation has been cancelled”
18 September 2011 23:03

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... Read More...

by ACALVETT | with no comments

Filed under: Tips, Performance, Analysis Services, SSAS, 2008, Windows 2003, Processing, SSAS 2008, Operation, Cancelled, W2K3

SQL Agent and SSAS Commands
30 May 2011 15:27

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... Read More...

by ACALVETT | with no comments

Filed under: Tips, SSAS 2005, Analysis Services, SSAS, 2008, Locking, SSAS 2008 R2, 2008 R2, Blocking, Default Database, Processing, SQL Agent

Parallelism, CPU Time & DMV’s
30 May 2011 12:04

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... Read More...

by ACALVETT | with no comments

Filed under: SQL 2005, Tools, Tips, Performance, Database Engine, 2008, 2008 R2, SQL 2008, CPU, Parallelism, DMV

SSAS Exposes a W2K3 bug
06 April 2011 23:05

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... Read More...

by ACALVETT | with no comments

Filed under: Tips, SSAS 2005, Analysis Services, SSAS, 2008, SSAS 2008 R2, 2008 R2, SQL, Stability, Windows 2003, FileSystemCache

SSAS Deployment essentials
30 March 2011 00:50

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... Read More...

by ACALVETT | 2 comment(s)

Filed under: Tools, Tips, Performance, SSAS 2005, Analysis Services, SSAS, 2008, SSAS 2008 R2, 2008 R2

Server Side Aliases
07 November 2010 21:37

Over the years i have come across a few situations where server side connections to SQL server fail when you use a DNS alias that points back to the server your initiating the connection from but you can connect remotely. Its an annoying problem which has a very unhelpful error message that changed in different versions of SQL. In SQL 2000 you are presented with... Read More...

by ACALVETT | with no comments

Filed under: SQL 2005, SQL 2000, Tips, Database Engine, 2008

SSAS <PreAllocate>: What you should know
18 July 2010 22:25

Preallocating memory for SSAS running on Windows 2003 is a good thing but as with all good things it is essential to know the behavioural changes you will experience, some of which may not be so obvious. My observations relate to SSAS 2008 SP1 CU8 running on Windows 2003 SP2. Why PreAllocate? In my opinion there are 2 reasons which i detail below. The first is... Read More...

by ACALVETT | 4 comment(s)

Filed under: Tips, Analysis Services, SSAS, 2008

SSAS – Synchronisation performance
08 April 2010 00:15

I’ve always thought of SSAS synchronisation as a clever file mirroring utility built into SSAS and i have never considered the technology as bringing any performance gains to the table. So, its a good job I like to revisit areas…. :) I decided to compare the performance of robocopy and SSAS Synchronisation between 2 Windows 2003 servers running SSAS 2008 SP1... Read More...

by ACALVETT | 2 comment(s)

Filed under: Performance, Analysis Services, SSAS, 2008, Synchronization, Synchronisation