Archive

Archive for the ‘Tips’ Category

Automating process monitor data collection

July 10, 2014 Leave a comment

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.

Read more…

Exposing the SSAS file system with the Metadata Sniffer

April 19, 2014 Leave a comment

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.

Read more…

SSAS Free drive space command

October 19, 2012 3 comments

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.

clip_image001

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 Smile).
  • 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

October 9, 2012 Leave a comment

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…

Working with SSAS traces and the dreaded Textdata in SSMS

February 4, 2012 Leave a comment

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!

Read more…

SSAS Crash Dump behavior

January 17, 2012 Leave a comment

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.
Categories: Analysis Services, Tips

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…