SSAS Performance tips – Lessons Learned – Part 2

יום שלישי, מאי 18, 2010

On a previous post I wrote about how the number of databases in an SSAS instance can make an impact on the overall performance of the instance. In this post I want to discuss another, more “explainable” property that you can set in order to improve cube processing performance. Trying to optimize processing time of our databases I set up a performance monitor trace to identify if we are having any resource bottlenecks that we need to address. Looking at the “Physical Disk: Disk Writes Bytes/sec” counter I noticed that it recorded a fairly small numbers most of...
תגובה אחת

SSAS – Cache Usage and Profiler

יום שישי, מרץ 19, 2010

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>...
תגובה אחת

Does “Not Supported” mean “Not Possible”? – SSAS 2008 upgrade plan…

יום שני, מרץ 8, 2010

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...
תגיות: , ,
אין תגובות

DMVs for Analysis Services

יום רביעי, ינואר 27, 2010

Not too many people know that they can query internal performance and resource related data for SSAS using DMVs. Yes, I’m talking about similar DMVs to the much more known relational database DMVs, only these DMVs can be queried by connecting to an SSAS instance, open a new MDX or DMX query window (either way will work for you) and type a simple SELECT statement which retrieves the information from the DMVs. These DMVs are actually metadata that is kept internally as schema rowsets and you can simply query them as if they were plain relational tables. There are...
אין תגובות