Archive for the ‘Profiler’ Category

SSAS 2008 R2– Little Gems

December 3, 2010 Leave a comment

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 preceding 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 Open-mouthed smile. You may be wondering how it is useful to know what files are being accessed but when your trying to understand what’s 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!

SSAS 2005 – Server side tracing starter kit

April 7, 2008 4 comments

Analysis services 2005 (SSAS) added the ability to trace server side events and i have used this feature a number of times. To date i had always used the profiler gui to do the SSAS tracing but today i found myself needing to initiate and manage a trace with scripts.

The good news is that it can be done! It did take a while to piece together how to do it though and i found some of the information quite a challenge to find so i am sharing with you the results and have attached a zip file with the necessary scripts.

So, what did i want to achieve?

  • A script that would create a trace on the server and log to a specified directory similar to the way you can with a SQL Server trace.
  • A script that would list all running traces on an analysis server.
  • A script that would destroy a named running trace, in my case the one i created.

Now, the script that creates the trace is likely to require editing each time to add new events as the script i am attaching only captures command events. The easiest way to define your events is detailed below.

  1. Open SQL Server profiler and define the SSAS trace you require.
  2. Next script the trace by going to “File – Export – Script Trace Definition – For Analysis Services 2005”.
  3. Open the script file and cut & copy the Events & Filter elements into my attached script ensuring you replace the existing Events & Filter elements.

Some people might be wondering why i needed to create the script file if i can script it from profiler? Well, profiler only scripts the events and filters and excludes options such as LogFileName, AutoRestart etc.

So, with the events in place you should now update the LogFileName element with your filename & path and check the LogFileSize element is appropriate. Finally, there is a StopTime element that you can uncomment and set which sets a time for the trace to automatically close but do not forget its the time at the server you are setting not the time where you are.

With all the updating done just run the script to create your own server side SSAS trace. It does not end here though because you will need to stop the trace manually if you have not enabled a StopTime. This is where “Delete Named Trace.xmla” comes in. Simply update the name element and run the script to delete the trace. Unlike SQL Server you do not need to stop and then close the trace. If you are not sure of the name of the trace you can run the script “List all server side traces.xmla” which is also useful for validating that you have removed the trace or that it auto closed. The list traces also gives useful information such as where the traces are outputing their results.

The trace script was amended from an example in Analysis Services Processing Best Practices and i would definitely recommend reading the article. The other scripts i hacked together and are very simple as i am a xmla novice.

I hope you find this information useful, you can locate the scripts here.