Archive

Posts Tagged ‘Tips’

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…

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

SSAS Deployment essentials

March 30, 2011 Leave a comment

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.

Read more…

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!

Analysis Server appears to hang…..

April 5, 2009 4 comments

We had an on going problem where by users would suddenly start complaining that the Analysis Server was hanging. When investigating the box we could see that there appeared to be no physical constraints. Disks were a bit above average, CPU was very low and there was plenty of memory.

When looking at the server through the SSAS activity viewer (part of the community samples) we could see that users were definitely queuing up and many of the queries should have returned in less than a second but were hanging around for ever (30 minutes or more). It was as if we were experiencing some form of blocking…….

To compliment our monitoring we use ASTrace (also part of the community sample) to do real time logging of what’s happening on the SSAS server to a SQL Server and amongst the capabilities it gives us is the ability to run a little procedure to show us what mdx/xmla is running with duration etc (its much more friendly than the activity viewer). So, when we were experiencing the trouble our script showed that an MDX query that touched multiple partitions totalling 100’s of gb’s appeared to be at the head of the chain every time.

Read more…

The joy of template parameters

July 15, 2007 Leave a comment

I thought I would do a quick blog about template parameters since I have been writing allot of standard deployment scripts for our 2005 builds and have used them extensively.

So what are they? Well they are place holders and you would you use them in the same places that you would probably put a variable. The key difference comes when you assign the values. With variables you work though the code setting the values as appropriate, with template parameters all you do is press CTRL-SHIFT-M and you get the dialogue box shown below. Simply fill in the values, click ok and your ready to run your script.

The format of a parameter is <Parameter Name, SQL Data type, default value> so to get the Mirrored DB name shown in the screen shot you would enter <Mirrored_DB_Name,nvarchar(128),‘My_Mirror’> where you wanted the value to appear.

This feature is available in Management Studio and Query Analyser, if you have ever used one of the standard Microsoft query templates you may have noticed that parameters are used in them as well.

I only realised template parameters existed a few years back because someone pointed it out to me. If this is the first time you have come across them (or had forgotten about them) I hope you find them as useful as I do.

Categories: SQL Server, SSMS