When Analysis Services Wakes Up Part Two
Reposted from Thomas Ivarsson blog with the author's permission.
This is the second part of a series of introductions to performance tuning of Analysis Services. The main tool used in this blog post is SQL Server Profiler that we will use to see what happens when SSAS starts up after a restart of the service and when the first user connects to SSAS.
Like I showed in the first part was the loading of files that starts when you connect to SSAS with Management Studio (MMS) but before you start a new MDX query. This is shown in the File Load Events in the picture below.
![clip_image002[6]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image0026.png "clip_image002[6]")
If the first user connection to SSAS by a client tool like Excel a lot of more events will fire off so in order to digest different groups of events my advice is to start with Management Studio.
What I did not mention in the previous post was that this is Meta data being loaded and it will always be initialized by the first user connection to SSAS after a service restart. Thanks to Akshai Mirchandani in the SSAS team for clarifying this.
This will also happen after a cube has been changed so a new Meta data file version needs to be loaded. You can try that by running a full process of all cubes and dimensions in a database-
This file loading process will load Meta data for all cubes databases that you have deployed on the SSAS server so the number of files depends on that. If you have a lot of cube databases deployed on the SSAS server the loading of this Meta data can take significant time.
The next step is to continue from Management Studio and start a new MDX query that will trigger some new events. In order to see this you will need to add the “Execute MDX Script Begin”- and End events to the Profiler Trace. The scripts are loaded into memory from the MDX Script file .These events will not be triggered when you connect to SSAS from MMS like we did initially. It is only when you start a new MDX query in MMS that these events will be triggered.
Actually you will see the file with the MDX scripts being loaded first.
\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2.0.db\Adventure Works.0.cub\MdxScript.10.scr.xml
![clip_image004[6]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image0046.png "clip_image004[6]")
Execute MDX Script Begin/End will also happen each time you clear the Cache. If you have Query dimension and Calculate Non Empty events activated in the trace they will appear together with the MDX Scrip Begin Events but this depends on the MDX Script events being evaluated internally(according to the MS White Pape:” Identifying and resolving MDX query performance bottlenecks in SQL Server 2005 Analysis Services”).
If the first connection to SSAS is from a tool like Excel a connection to a specific cube database and cube have been defined first and this means that not all meta data files for all cube databases and cubes will be opened. Management Studio will open up all files that are related to all databases under the SSAS server.
Progress Report Begin Events after Excel 2010 is connected to SSAS 2008 R2.
![clip_image006[6]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image0066.png "clip_image006[6]")
When all files are loaded together with the MDX script let us clear the cache by running this command.
![clip_image008[10]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image008101.jpg "clip_image008[10]")
After running the XMLA command Clear Cache we call the cube again by running this MDX query.
![clip_image009[10]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image009101.png "clip_image009[10]")
That will trigger that the MDX script will be loaded again. This is a sample of the Profiler events being triggered.
![clip_image011[6]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image0116.png "clip_image011[6]")
The Windows O/S has a file system cache that SSAS can benefit from by not having to read files from the I/O system but from memory which is must faster. The file system cache is never cleared with the XMLA Clear Cache command.
If I clear the file system cache with the SSAS stored procedure without a restart of the SSAS service, the clearing of the file system cache will not unload the MDX scripts from memory. The Execute MDX script will not reappear.
![clip_image012[10]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image012101.png "clip_image012[10]")
![clip_image014[6]](http://thomasivarssonmalmo.files.wordpress.com/2011/12/clip_image0146.png "clip_image014[6]")
The loading of the meta data files that I mentioned first in this blog post are not affected by the clear file system cache stored procedure.
You download this stored procedure from www.codeplex.com and install it on your SSAS Server as a new assembly. I have my doubts on if you will be allowed to install it on a production system so I recommend using it on your own development sandbox.
|
|
Thomas Ivarsson has been working with the MS BI platform since SQL Server 7 in 1999. Presently he is working in the telecom industry in Sweden, with a data warehouse based on SQL Server 2005. From 1999 to 2007 he worked as a consultant also on the three SQL Server BI platforms. During the latest years he has spent most of time on SSAS, Reporting Services, ProClarity and Performance Point. He also has several years experience of the ETL process with DTS and SSIS. During 2008 and 2009 he has been working with introducing data mining in his daily business to see patterns in a service network behaviours. His blog can be found here: http://thomasivarssonmalmo.wordpress.com/ |
Tags: mdx, management, performance