SQL Centre of Excellence

Its hard to do capacity planning for MOLAP cubes and a common question asked is if we have enough memory or why the server is showing zero bytes free memory wise.

While the msmdsrv.exe process can consume a lot of memory between the storage engine cache and formula cache, we need to be super careful to ALSO leave space for the FileSystemCache which appears as the “Standby” are within resource manager.

Its perfectly possible that SSAS has enough memory, but the FileSystemCache is starved of memory so any large queries will go to physical disk rather than to the cache which is “considerably” slower.

The screenshot below shows a Server with lots of memory after a load test of 100,000 queries. Note the FilesystemCache is > than the msmdsrv.exe memory!

image

You may find that SSAS has plenty of memory free but your server still has zero bytes free due to a massive “Standby” cache. You have two options:

a) Add more memory

b) Limit the FileSystemCache by using the LimitSystemFileCacheSizeMB setting in the ini file.

If you limit the cache be aware that IOPS from the FileSystemCache are super fast compared to disk, so you may get a performance hit.

 

Two lessons for me

1. Don't just use the commit size for the msmdsrv.exe when looking at memory usage for analysis services.

2. Maybe for cache warming we should look to warm up the FileSystemCache first. This would be considerably faster if we could leverage large block sizes and sequential IO rather than the small random IO that the SSAS storage engine does. Hmmm going to think about how to do this now…

Thanks to everyone who made it to my session at SqlBits. I’m hanging around the event so feel free to ping my on twitter (@bob_duffy) if you want to chat about Cube Processing over a beet at the event. I love discussing the challenges of cube processing and swapping notes on how to optimise !!

Attached is a copy of my slides

A few people wanted a copy of the Test Harness too. The test harness does the following:

- runs XMLA command to start trace

- run logman to start performance counters

- run XMLA to process cube

- stop performance counters

I have a few speaking events lines up for the rest of the year.

www.SqlBits.com– 17th-19th July

Sql Saturday Oslo, 30th August 2014

SQL Saturday Dublin, 19th/20th Sept 2014

  • World Jenga Championships Ireland v Denmark.
  • Lightning Talk – 5 minutes “call my SQL bluff”

PASS Summit 2014, Nov 4th/7th 2014

  • Load Testing Analysis Services

SQL Server Days (Belgium), Sept 3th/1st Oct 2014

Hope to see you at one of the events!

If you are coming in from Dublin Airport there are a few options on getting to the Hilton hotel, Charlemont for Sql Saturday Dublin 2014

http://www.sqlsaturday.com/310/

By Taxi

There is a taxi rank to your right as you exit the terminal at Dublin Airport. It's well managed by Dublin Airport staff and any queues move pretty quickly. A taxi to Charlemont Place will cost you around €30. the Hotel can help you book a taxi back.

If you are travelling around Dublin and want to book a taxi there is an excellent App for mobile phones to book and track taxis. You wont need this at the Airport as they have a 24 hour taxi rank.

https://www.hailocab.com/ireland

By Aircoach and Luas

There is a dedicated coach between the airport and city centre that is very comfortable and has WIFI. It also runs 24 hours and has GPS tracking on its web site to see where the coaches are!

From the city centre there is a very good light rail system and the Hotel is only two stops south of the city centre (Stephens Green)

take the Aircoach Leopardstown service to Kildare Street and then walk around the corner to the LUAS stop at St. Stephen's Green (just at the top of Grafton Street). It's about 5 mins walk. From there you can take the LUAS two stops to Charlemont which is right by your hotel.

The Aircoach as far as Kildare Street costs €8 single per adult and €14 return. The LUAS costs €1.50 each way.

Just walk outside the airport and you'll see blue signs for the Aircoach to Leopardstown.

www.luas.ie

www.aircoach.ie

By Bike

Ok you cant actually cycle from the Airport to the Hotel, but if you are sight seeing there is an excellent bike rental system in Dublin where you can rent bikes and drop them off at set points.

The site below shows you where the pickup points are. There is one bike station right outside the hotel.

http://www.dublinbikes.ie/

The speaker line up for #SqlSatDublin is announced with some 29 speakers including:

  • 7 Speakers from MSFT including a principal architect on the SQL team.
  • 15 MVP’s from around the globe.
  • 3 SQL MCMs.
  • 2 Instructors from the SSAS Maestro course.

image

 

To the see the schedule and register follow this link

http://www.sqlsaturday.com/310/eventhome.aspx

 

 

See you there!

Bob

Update: 23/06/2014. Sorry we missed the MVP flag from a few speakers! That's updated to 15 MVP’s speaking at #SqlSatDublin

Five preconference's are open for #SqlSatDublin for Friday 19th of September, 2014.

At €299 for a whole day workshop with industry experts from around the globe this will be the best whole day training SQL training you will find in Ireland for 2014.

Revenue from this training day all goes to help deliver #SqlSatDublin, Irelands largest SQL Server event in 2014.

Click on the link below to view more details.

You can read more and book these whole day workshops at:

http://www.prodata.ie/events/SqlSat310/

and you can sign of the FREE SQL Saturday event at

http://www.sqlsaturday.com/310/

We are also super excited that we have the final pre-conference workshop to announce shortly which you will not want to miss if you are developing or deploying solutions on the cloud platform. Stay tuned…

I’m a huge fan of RML utilities for SQL DB Engine workload analysis. Its a fantastic tool fro being able to capture a trace and point  a finger at offending users, applications, batches, queries and even see plans and resource usage.

You can download RML from this link

http://www.microsoft.com/en-us/download/details.aspx?id=4511

One issue is that the Report viewer hyperlinks don't work out of the box on Windows 8.1. A Patch is needed for the SSRS 2012 Report viewer component which you can download here.

https://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=37409

The quality of RML utilities is one of the biggest reasons that our SQL consultants (including myself) are slow to adopt extended events in a mainstream fashion. While extended events can have great instrumentation they still lack the presentation layer and analytics that RML brings. Who knows, maybe we can convince CSS to write an extended  events version of RML utilities – that would change the scene over night!

Its no secret that optimising distinct count in SSAS/MOLAP is painful. the normal optimisation stuff is covered in various papers such as:

- INI file settings to increase PageSize

- Separate Measure Groups

- Trying to partition by the distinct count item so that each partitions min and max values don't cross over.

We recently did all this for a customer but noticed one annoying thing. When we fired up SQL Profiler and ran a simple query we are flooding with “Progress Report End” noise, hitting the same partition.

image

In the case of one customer this generated over a million profiler events even though they only had 150 or so partitions. On the blown up adventure works cube with 150 million rows this generates some 1,500 events when there are only four partitions!

I chatted with Microsoft CSS and Alex Whittles who has done some interesting benchmarks on Distinct Count (http://www.purplefrogsystems.com/blog/2014/03/analysis-services-tabular-or-multidimensional-a-performance-comparison/)

The reason for this seeming annoying flood is that the Distinct Count outputs a progress report message PER SEGMENT and it pretty much has to scan all segments.  I’m cool with it having to scan all the segments (this is part of the challenge of the algorithm), but does it “really” have to give a message to profiler every 64k or so…

So far feedback from MS is that this behaviour is by design. I can’t help but think that such verbose instrumentation must hurt performance somewhere.

If you do notice this. Don’t panic – its by design. of course there are many funky ways to avoid a physical distinct count but that’s another blog!

 

A filed a connect item to ask if the product team can tone down the verbosity on events for Distinct Count. Please up vote if you come across this.

https://connect.microsoft.com/SQLServer/feedback/details/878199/progress-report-flooding-with-distinct-count-molap

Bitmap Indexes (*.map files) play an important role within Analysis Services. They provide a mechanism for the storage engine sub cube event to efficiently locate the relevant segments within the fact file without having to scan the whole thing. This is how you can have a 20 GB *.fact file in the partition but still get storage engine events of only 50ms or so.

However, it can be a best practise to turn off Bitmap Indexes in a few scenarios:

a) Where the attribute is not used much.

b) Where the attribute is really just a related property of an existing attribute

c) where the cardinality of the attribute is pretty much the same as the key

Further reading on this is below

http://www.mssqltips.com/sqlservertip/2567/ssas--best-practices-and-performance-optimization--part-3-of-4/

http://www.ssas-info.com/analysis-services-papers/1216-sql-server-2008-white-paper-analysis-services-performance-guide

One word of warning is when you turn off/on the bitmap indexes by adjusting the AttributeHierarchyOptimized property this may not actually have the desired effect.

The key thing we need to be aware of is that this AttributeHierarchyOptimized Attribute can be set in TWO places and just changing it in the shared dimension may not actually change it for your cube:

  • Once in the shared dimension
  • Once in the cube dimension

1) Changing AttributeHierarchyOptimized on the shared dimension

image

 

2) Changing AttributeHierarchyOptimized on the a cube dimension

image

 

This caused havoc on a recent tuning project where we turned off too many bitmap indexes in the normal dimension editor and then didn’t realise that when we turned them back on in the same place they were now stuck in the cube in the “off” state.

Now this behaviour is pretty much by design. The intention is that turning off bitmap indexes in the shared dimension will turn them off for all cubes, but if you leave them on you are free to turn them off on specific cubes. however it can lead to some confusion!

Speaking at #SqlBits

by Bob Duffy 17. April 2014 08:20

Just got the emails and I’ve been selected to speak at SqlBits on the 18th and 10th of July. Looking forward to Europe's largest SQL event!

https://sqlbits.com/

My Sessions are:

  • Friday - Optimising Cube Processing
  • Saturday - Migrating to the Cloud

In addition I am making a special guest appearance in Carmel's Session as “data monkey”:

  • Saturday – The Irish Economic Crisis Visualised with Power BI

Now to think up some funky ideas for the steam punk costumes.

Page List

Page List