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 Performance tips – Lessons learned – Part 1

יום שבת, מאי 15, 2010

I’ve been researching a few performance issues lately on an Analysis Services 2005 instance with 20 (almost) identical databases on it, each weighing roughly 2.5GB and containing 6-7 cubes with approximately 400 partitions in each cube. As you can imagine, this makes up quite an impressive number of files stored on disk. SSAS stores everything on files, and unlike its relational relative (no pun intended), which will store hundreds of GB of data on a fairly small number of large files (I know this is an over simplification of how one should plan for data file allocation on SQL...
2 תגובות

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

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