Archive

Archive for the ‘Tips’ Category

Two new Analysis Services Blogs

September 18, 2011 Leave a comment

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!

Advertisements
Categories: Analysis Services, Tips

SSAS 2008 R2 Operations Guide

June 2, 2011 Leave a comment

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.

Categories: Analysis Services, Tips

SQL Agent and SSAS Commands

May 30, 2011 Leave a comment

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 but it really does matter. Below are extracts of a processing task from profiler which was executed using a SQLAgent task.

image

So, what is happening here? Read more…

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!

SSAS Native v .net Provider

November 30, 2010 Leave a comment

Recently I was investigating why a new server which is in its parallel running phase was taking significantly longer to process the daily data than the server its due to replace.

The server has SQL & SSAS installed so the problem was not likely to be in the network transfer as its using shared memory. As i dug around the SQL dmv’s i noticed in sys.dm_exec_connections that the SSAS connection had a packet size of 8000 bytes instead of the usual 4096 bytes and from there i found that the datasource had been configured with the wrong provider but what was really interesting and the point of the blog is the performance difference which i have shown below.

Rows per second
.Net SqlClient Data Provide 30,000
SQL Server Native Client 10 240,000

That’s right! For a single partition, the native client was able to process 240,000 rows per second where as the .net client maxes out at 30,000. That means the SQL Native Client is 800% faster! I knew that the .net providers were slower but I had never gathered any metrics before. If your looking after a SSAS server I would definitely recommend taking a few minutes to check which provider is configured in the datasource.

Another point to consider is you may have a custom solution that is doing your ETL and utilising the .net providers. This would also be impacted by the .net provider throughput limits and a switch over to SSIS could dramatically improve your ETL.

SSAS 2008 – INI Files and in place upgrades

September 24, 2009 Leave a comment

Being the suspicious person i am i wondered if there would be any differences in the MSMDSRV.ini of an instance upgraded from 2005 as opposed to a clean install.

Now obviously i expect an in place upgrade to preserve my settings and add any new ones because it should not overwrite anything since i might have change from defaults for a good reason…….

Below is what i found followed by my thoughts….

IN Place Upgrade Value (Effectively 2005)

<ServerSendTimeout>-1</ServerSendTimeout>

<ServerReceiveTimeout>-1</ServerReceiveTimeout>

<AggregationPerfLog>1</AggregationPerfLog>

<DataStoreStringPageSize>8192</DataStoreStringPageSize>

<MinCalculationLRUSize>20</MinCalculationLRUSize>

<MdxSubqueries>3</MdxSubqueries>

2008 Clean Install Value

<ServerSendTimeout>60000</ServerSendTimeout>

<ServerReceiveTimeout>60000</ServerReceiveTimeout>

DELETED

<DataStoreStringPageSize>65536</DataStoreStringPageSize>

DELETED

<MdxSubqueries>15</MdxSubqueries>

Looking at what has changed they appear to be settings which may well have been tuned as a result of lessons learnt at Microsoft. The removal of AggregationPerfLog i suspect is cosmetic and the setting probably does nothing since there is another one called AggregationPerfLog2 which i suspect replaces it. Its also quite likely the same is the case with MinCalculationLRUSize.

An important thing to take away here is that an in place upgrade may not perform/behave the same way as a clean install because by default its ini file is going to be different. In my case i`m checking out the impact of the settings with a view to adding a step to our upgrade path to change them to the clean install values.

One more thing to get you thinking. If the settings did change based on lessons learnt, maybe its worth porting these back to 2005 and taking them for a spin…………. Test test and test some more!