Tag Archives: query optimization

SQL Server Data Mining and Apollo Columnstore Indexes

Note: This post was revised November 12, 2010 to clarify the brand names Apollo and VertiPaq (thanks Denny Lee of SQLCAT) — and I extended comments on Amir Netz’s C++ versus C# analogy which I believe clarifies the discussion between what I have termed managed and unmanaged aggregations.  

This week’s PASS Summit conference included several demonstrations and announcements of the next version of SQL Server, version 11, codenamed Denali. In this blog post I have the following goals:

  • Outline Apollo columnstore indexes as a competitive Microsoft technology
  • Respond to Microsoft claims about the comparative performance advantages of columnstore indexes specifically for aggregations
  • Respond to Chris Webb’s multiple blog posts (posted from Seattle, WA) about the future of SQL Server Analysis Services

These topics seem like a lot to take on in one blog post, but in context, Microsoft found a way to introduce columnstore indexes in an 8 page whitepaper. As regular blog readers know, I put on my scientific hat first when trying to distinguish science from science fiction…

Continue reading “SQL Server Data Mining and Apollo Columnstore Indexes” »

Architecture of Query Execution — Retrieving Data from Storage

510cHjGCsUL. SL160  Architecture of Query Execution    Retrieving Data from StorageMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 30

An earlier chapter discusses how Analysis Services saves data in files. This chapter talks about how SSAS retrieves information from these disparate files to fulfill query and processing requests on different types of partitions and dimensions.

The chapter to me reads like product documentation for the processing techniques used for retrieving data. Different processes happen depending on the situation:

  • Querying regular measure groups (page 556)
  • Querying ROLAP partitions (page 559)
  • Querying measure groups with DISTINCT_COUNT measures (page 560)
  • Querying remote partitions and linked measure groups (page 563)
  • Querying measure groups with indirect dimensions (page 564)

I provided the headers for reference so that you would know what this chapter discusses. However, in reading this chapter I did not see any properties or advice on improving performance. Sometimes the chapter talks about performance disadvantages of certain designs, and when this performance hit comes in retrieving files, that fact would be relevant to this chapter. In the previous chapter reviews, I provided links on what can be changed or optimized for performance. This chapter is the last one of the section, and since there is no actionable advice, I would have preferred this material to be appended to one of the other chapters in this section.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6

Architecture of Query Execution — Calculating MDX Expressions

510cHjGCsUL. SL160  Architecture of Query Execution    Calculating MDX ExpressionsMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 29

This chapter starts with a claim which should make everyone happy: “Optimization of MDX calculations was one of the most important goals of Analysis Services 2008.” (page 527) This focus on MDX performance leads into a discussion of the following topics:

  • Query execution stages
  • Parsing an MDX request
  • Creation of calculation scopes
  • Building a virtual set operation tree
  • Optimizing multidimensional space by removing empty tuples
  • Calculating cell values
  • Cache subsystem

The book mentions a user guide created specifically for SSAS 2008: Performance Improvements for MDX in SQL Server 2008 Analysis Services. I like the fact that they took the essence of their key MDX advice and put it into one free page. This online resource mentions that “increased performance” has been achieved in a number of areas, and that claim is consistent with the essence of chapter 29. Included on this page are the VBA functions ported to SSAS, and even those VBA functions have improved performance. They recommend manually auditing MDX code for maximum performance, and they provide tips and suggestions of what approaches have a performance hit.

Another webpage provides tips on a complementary topic: Querying Multidimensional Data (Analysis Services – Multidimensional Data. I will also add links to the MDX reference:

Queries can be monitored using SQL Server Profiler.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6