Optimizing Bitmap Indexes during Partition Processing in SSAS Multidimensional

When MDX query is issued on SSAS Server it goes through Formula Engine which request data from Storage Engine, and it either retrieves data either from cache or aggregations or from partition/fact data files (see this article for Understanding MDX Query Execution process in detail). When data is not available in cache and aggregations there is only one way for storage engine to provide data requested by formula engine and that is to scan MOLAP partition data files and partition segments. This is the most expensive way to provide data. To speed up this process SSAS provides Bitmap Indexes, these are used to eliminate rows from partition files so that very less number of partitions segments are scanned resulting in better performance. Let us see with the help of example how bitmap indexes work. Read more Optimizing Bitmap Indexes during Partition Processing in SSAS Multidimensional

Bulk Loading data into COLUMNSTORE table

Bulk loading is the most performant way to move data into a columnstore index because it operates on batches of rows. Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. Only rows at the end of a load that don’t meet the minimum of 102,400 rows per rowgroup go to the deltastore. For understanding bulk load and minimal logging see Fastest Data Loading using Bulk Load and Minimal Logging
Read more Bulk Loading data into COLUMNSTORE table

Bulk Loading data into HEAP versus CLUSTERED Table

Bulk Loading into HEAP

Generally, the fastest way to load data into SQL Server table is by Bulk Loading data into HEAP using TABLOCK hint on destination table which facilitates minimally logging. For more details see Fastest Data Loading using Bulk Load and Minimal Logging 
The few constraints for loading data minimally logged using TABLOCK hint:

  • The database must be in simple or bulk logged recovery model.
  • The destination table must be without Clustered Index.
  • There must be no NON-Clustered index on the destination table.

Read more Bulk Loading data into HEAP versus CLUSTERED Table

Optimizing (Fact) Partition Processing in SSAS Multidimensional

The performance goal of partition processing is to refresh Cube/OLAP data in an efficient manner that does not negatively impact the query performance of dependent partitions. The following techniques for accomplishing this goal are discussed in this section: Read more Optimizing (Fact) Partition Processing in SSAS Multidimensional

MDX Solutions: SORTING using ORDER and TOPCOUNT functions

As with most applications there are many different ways to complete a task and sorting in MDX is no exception. Furthermore, Analysis Services utilization of hierarchies further complicates the method and implementation of sorting routines. SSAS actually has several potential layers of sorting, we can set the sorting at the dimension level or force sorting during query run time. For dimension attributes, we can actually set the Order By property in SSDT to either a dimension attribute Name or a Key as displayed below for the Product attribute under the DimProduct dimension. Additionally, the sort order defined on the dimension attribute property screen can only be the name or the key and is always in ascending order. Read more MDX Solutions: SORTING using ORDER and TOPCOUNT functions

Optimizing Dimension Processing in SSAS Multidimensional

The performance goal of dimension processing is to refresh dimension data in an efficient manner that does not negatively impact the query performance of dependent partitions. The following techniques for accomplishing this goal are discussed in this section:

  • Reducing attribute overhead.
  • Optimizing SQL source queries.

Read more Optimizing Dimension Processing in SSAS Multidimensional

Fastest Data Loading using Bulk Load and Minimal Logging

Understanding Bulk Load Operations

There are two types of INSERTs in SQL server, first is singleton INSERT which inserts rows one by one and second type is BULK INSERT which feeds a continuous stream of data into a table, so this type of INSERT is much faster than singleton inserts. Read more Fastest Data Loading using Bulk Load and Minimal Logging

CS #1 Using SQL Hint OPTION RECOMPILE for improving performance

Problem Statement 

Stored procedure CS1_RECOMPILE is running slow for some input parameters combinations and running fine for other input parameters.

CREATE PROC CS1_RECOMPILE 
     @start_prod_id INT,
        @end_prod_id   INT
AS
  BEGIN
    SELECT TOP 10 ProductId
    FROM bigProduct
    ORDER BY ListPrice DESC;    

    SELECT TOP 10 *
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;
 END;
GO

Read more CS #1 Using SQL Hint OPTION RECOMPILE for improving performance