I recently was required to check on the performance of an SSAS instance. We had a new OLAP model with several MDX queries that we wanted to ensure were performing adequately. Now, you may know that SSAS is using caching in order to speed up its response time. Initial queries are directed to disk and once data is first read, it is then being placed in cache to serve future queries faster, eliminating the need to access disk again.
What we did first was to clear the cache, using the following XMLA command:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<object>
<DatabaseID>DBNAME</DatabaseID>
</Object>
</ClearCache>
</Batch>
Where the “DBName” stands for our OLAP database. Next we started hitting the server with MDX queries and ran Profiler against the instance to gauge response time and query behaviour. We were using the “Query Begin” and “Query End” events as well as the “Get Data From Cache” and “Query Subcube Verbose” to monitor cache behaviour.
We ran Windows Performance Monitor in the background as well, with the following counters:
1. Total Queries From File: this counter tells us the number of queries being serviced by the file system, i.e. before data is cached.
2. Total Queries From Cache: this counter tells us the number of queries being serviced by the cache.
When we ran the first MDX query, we saw an increase in the “Total Queries From File” counter and the next time we ran the same query we saw an increase in the “Total Queries From File” counter, which is exactly the behaviour that we were expecting.
However, looking at Profiler, we noticed that the “Get Data From Cache” event was raised even when we ran the MDX query for the first time. We can provide an answer to this seemingly abnormality by looking the “Query Subcube Verbose” event. When running the query for the first time, you will see an indication that the engine queries “Non-cached data” first and that issues a query on “Cached Data”. The second time you run the MDX query, we only see the "Cached Data” query events being raised. So, the way this all works is like this: the Query Processor engine will always execute its queries from cache. This is why we will always see the “Get Data From Cache” event raised, regardless of the fact cache was cleared or not to begin with. However, when no data is in cache, it will trigger a “Non cached data” query first, the Storage Engine will fetch data from disk, place it in cache, and the the Query Processor engine will query this newly fetched cached data.
Hope this will help anyone who is doing SSAS performance monitoring!
-Yaniv
(This is post number 16 for week number 16 of my SQL blog challenge)
I was reading through the (excellent) “SQL 2008 Upgrade Technical Reference Guide” paper from Microsoft the other day, which describes in great detail all the best practices of how to go about and upgrade your existing SQL Server 2000 and 2005 installations to SQL Server 2008. I was especially interested in the SSAS upgrade section, as I’m in the process of planning such an upgrade in a highly sensitive and data-intensive SSAS 2005 environment, so I wanted to be sure I was not overlooking anything important.
Initially I was thinking of performing a side by side upgrade, where you install a second instance of Analysis Services on the same box, and move the databases across to the new instance. Once I’m happy with the move and the way the databases perform on the new, 2008 instance, I was planning on switching off the 2005 service, and point the applications that use the OLAP databases to the new instance. However, after reading the first paragraph of the upgrade document I was slightly surprised to read that “for SSAS 2005, only an in-place upgrade is supported”. So I went through my initial plans and checked again:
1. Can I install an instance of SSAS 2008, side by side with an existing instance SSAS 2005? I certainly can and I even have my own laptop to prove it works.
2. Can I backup an SSAS 2005 database and restore it on a SSAS 2008 instance and will that database still be readable and not require any additional modifications? I certainly can. In fact, assuming this database does not contain any features which no longer exist in SSAS 2008 (and you need to ensure this is not the case any way, regardless if you’re upgrading in-place or side-by-side), the database will be available immediately after the restore on the SSAS 2008 instance is complete and you do not need to reprocess any dimensions or cubes.
So, there you have it: I could not come up with any good reason why this upgrade option is not supported. I continued reading, and learnt that Microsoft does support either an in-place upgrade or a side-by-side upgrade if you upgrade from SSAS 2000 to SSAS 2008. In order to perform a side-by-side upgrade from SSAS 2000, you will need to use the Database Migration Wizard which only works when the source database is an SSAS 2000 database. You cannot use this tool if you need to upgrade from 2005 to 2008, then again, you do not need to use this either, because you can backup and restore just the same.
There are pros and cons to performing an in-place upgrade versus a side-by-side upgrade. For example, when performing a side-by-side upgrade on the same box, you will need to accommodate storage for double the database size you actually need. You also have a new instance name which needs to be updated in the calling application. But there are advantages in performing a side-by-side upgrade as well, namely you control the process (which is so important when you deal with production environments). You can check each database, ensure functionality has not been impacted by the upgrade and move on to test another database until you’re done and happy with the outcomes. you also don’t impact the current production environment and the SSAS 2005 instance is up and running until you decide you’re ready to make the switch.
Happy upgrading!
-Yaniv
(This is post number 15 for week number 15 of my SQL blog challenge)
Technorati Tags:
SSAS 2008,
SSAS 2005