SSAS 2008 R2 Performance-Guide

Der SSAS-Performance Guide für Version 2008 R2 ist seit einigen Tagen verfügbar. Chris Webb fasst die Bedeutung des Guides prägnant zusammen: "if you are serious about SSAS you need to read it".

Hier noch eine Sammlung von Punkten, die mir erinnerungswürdig erscheinen (ohne jeden Anspruch auf Vollständigkeit):

• Performance-Vorteile geeigneter Attribute Relationships (S. 11)
• "Cross products between levels in the hierarchy do not need to go through the key attribute. This saves CPU time during queries."
• "Aggregations built on attributes can be reused for queries on related attributes. This saves resources during processing and for queries."
• "Auto-Exist can more efficiently eliminate attribute combinations that do not exist in the data."
• Flexible vs. Rigid Relationships (S.12)
• "When a change is detected during process in a flexible relationship, all indexes for partitions referencing the affected dimension (including the indexes for attribute that are not affected) must be invalidated. This is an expensive operation and may cause Process Update operations to take a very long time. Indexes invalidated by changes in flexible relationships must be rebuilt after a Process Update operation with a Process Index on the affected partitions; this adds even more time to cube processing."
• Reference Dimensions (S. 14)
• können materialisiert werden
• Probleme beim ProcessUpdate: "if you run a process update on the intermediate dimension, any changes in the relationships between the outer dimension and the reference will not be reflected in the cube. Instead, the original relationship between the outer dimension and the measure group is retained"
• Large Dimensions (S. 18)
• "If a dimension contains attributes where the total size of the string values (this includes translations) exceeds 4 GB, you will get an error during processing." Für Denali soll das Limit beseitigt werden.
• Partition Slicing (S. 20)
• Auto Slice: "when Analysis Services reads the data during processing, it keeps track of the minimum and maximum attribute DataID reads. These values are used to set the slice when the indexes are built on the partition." + "auto slice typically works best if the data contained in the partition maps to a single attribute value. When that is the case, the maximum and minimum DataID contained in the slice will be equal and the slice will work efficiently."
• Partition Sizing (S. 22)
• "partition size alone does not have a substantial impact on query speeds"
• sollte sich an administrativen Gesichtspunkten orientieren
• die BIDS-Warnung vor Partitionen mit mehr als 20M rows ist bedeutungslos
• Relational Data Source Design (S.26ff.)
• "Use a Star Schema for Best Performance"
• "Consider Moving Calculations to the Relational Engine"
• "Use Views"
•  Query Processor Cache (S. 35)
• Query Context, Session, Context, Global Context: "Even though a query references all three contexts, it will typically use the cache of a single context. This means that on a per-query basis, the query processor must select which cache to use. The query processor always attempts to use the broadly applicable cache depending on whether or not it detects the presence of calculations at a narrower context. If the query processor encounters calculations created at query time, it always uses the query context, even if a query also references calculations from the global context (there is an exception to this –
  queries with query calculated members of the form Aggregate() do share the session cache)."
• offenbar gibt es sehr viele Fälle, in denen der Global Cache nicht verwendet werden kann (MDX functions that are locale-dependent; "use of cell security; functions such as UserName, StrToSet, StrToMember, and StrToTuple; or LookupCube functions in the MDX script or in the dimension or cell security definition disable the global cache. That is, just one expression that uses any of these functions or features disables global caching for the entire cube."; visual totals; "Queries that use the subselect syntax (SELECT FROM SELECT) or are based on a session subcube (CREATE SUBCUBE)"; Arbitrary shapes); es bietet sich an, mit dem SQL Profiler zu prüfen, ob Standard-Excel-Zugriffe den Global Cache verwenden können, oder ob einer der Problemfälle dagegen spricht.
• Data Retrieval (S.38)
• Storage Engine (SE) sucht nach Daten in folgender Reihenfolge:
• SE Cache
• Aggregationen (I/O from disk oder OS-Cache)
• Partitionsdaten (I/O from disk oder OS-Cache)
• "storage engine cache is also known as the data cache registry because it is composed of the dimension and measure group caches that are the same structurally"
• vorhandene Inhalte können aggregiert oder gesplittet werden
• bei Memory-Engpässen wird der Cache geleert
• Aggressive Data Scanning (S. 39): SSAS führt manchmal recht massive Prefetch-Operationen durch (in der Annahme, dass der Anwender die Daten schon noch brauchen wird). Deaktivieren kann man das Prefetching mit "Disable Prefetch = 1" im Connection-String.
• Subspace Computation (S.40ff.): erläutert die Unterschiede zwischen Cell-by-cell evaluation und subspace computation. 
•  Cell-by-cell evaluation: "The 10 cells for [2005, All Products] are each evaluated in turn. For each, the previous year is located, and then the sales value is obtained and then added to the sales for the current year." (bei sparse data wird viel unnötige Arbeit geleistet; außerdem werden Zugriffe unnötig wiederholt)
• "In subspace computation, the engine works its way down an execution tree determining what spaces need to be filled."
• Expensive vs. Inexpensive Query Plans (S. 42): ein Plan ist expensive, wenn er Cell-by-cell evaluation enthält: IIf, CASE, and IF functions können expensive query plans hervorrufen. Eine Liste mit Gründen für eine Cell-by-cell evaluation folgt aus Seite 49ff.
• Optimizing MDX (S. 45ff.): liefert diverse Hinweise zur Query-Optimierung (Cache-Effekte ausschalten; SQL Profiler nutzen; Unterscheidung zwischen SE und FE Events; binäre Vereinfachung von Queries etc.) und auch einige wichtige Links. Hier nur die Punkte, diemir halbwegs neu waren:
• Avoid Assigning Nonnull Values to Otherwise Empty Cells (S. 50): leuchtet ein, denn SSAS "is very efficient at using sparsity of the data to improve performance. Adding calculations with nonempty values replacing empty values does not allow Analysis Services to eliminate these rows."
• Sparse/Dense Considerations with “expr1 * expr2” Expressions (S. 51): die Expression mit der höheren Anzahl von NULLs sollte vorne stehen: entspricht dem Vorgehen bei Nested Loops Joins, wo ebenfalls die kleinere Menge die Schleife treiben sollte.
• IIF-Optimierung mit den Hints EAGER und STRICT (S. 53).
• NON_EMPTY_BEHAVIOR: in 2008 in vielen Fällen nicht mehr nützlich (= wird oft ignoriert), und manchmal sogar ein Problem ("Eliminate it from the MDX script and add it back after performance testing demonstrates improvement")
•  Aggregations (S. 60ff.)
•  unnötige Aggregationen schädigen den Cache: "adding unnecessary aggregations can worsen query performance because the rare hits move the aggregation into the file cache at the cost of moving something else out"; außerdem: "there is a direct correlation between the number of aggregations and the duration for the Analysis Services storage engine to parse them"
•  Aggregations-Zugriffe werden im Profiler durch das "Get Data From Aggregation event" ausgewiesen
• Scale-Out (S. 69): Verteilung auf mehrere Server; z.B. zur Vermeidung des Blockierens durch langlaufende Queries.
• Tuning Processing Performance (S. 70ff.)
• Dimension Processing
• ProcessUpdate (S. 76) 
• erfordert Neuaufbau von flexible aggregations: "Note that ProcessUpdate drops invalid aggregations and indexes, requiring you to take action to rebuild the aggregations in order to maintain query performance. However, flexible aggregations are dropped only if a change is detected."
• kann sehr teuer sein: "When ProcessUpdate runs, it must walk through the partitions that depend on the dimension. For each partition, all indexes and aggregation must be checked to see whether they require updating. On a cube with many partitions, indexes, and aggregates, this can take a very long time. Because this dependency walk is expensive, ProcessUpdate is often the most expensive of all processing operations on a well-tuned system, dwarfing even large partition processing commands."
• ProcessAdd (S. 77): 
• "typically runs much faster than ProcessUpdate."
• mit Default Konfiguration gibt's Fehlermeldungen duplicate key beim Processing ("caused by the addition of non-key properties that already exist in the dimension": Anpassung der Fehlerkonfiguration erforderlich)
• Tuning
• Reduzierung der Dimensionsattribute
• Verzicht auf Bitmap Indexes
• ByTable Processing: liefert notwendigerweise duplicate key Fehler
• Splitting Processing Index and Process Data (S.84): wird immer noch empfohlen