Columnstore vs. SSAS

March 23, 2014

 

As part of a project that I have been engaged on recently, I have had the opportunity to test the performance difference between SQL Server 2014 Clustered Columnstore Indexes and SQL Server Analysis Services Multi-dimensional (MOLAP). To test the performance differences between the two technologies, I converted the three most CPU intensive MDX queries to T-SQL queries (Clustered Columnstore). Both sets of queries were then executed from cache, with a variety of predicate values, on identical hardware. Aggregate performance in seconds shown below:

clip_image002

It must be emphasized that the performance of Columnstore Indexes is dependent on many variables, including but not limited to calculation logic, number of distinct values, distribution of values, predicated values, and hardware IO throughput. In this case the queries involve the following calculations: exp(), sum(), and then log().

After testing the initial set of three queries, I then tested the performance of over 20 queries divided into 3 groups. These series of tests included several levels of aggregation with different predicate values as well.

clip_image004

Conclusion:

Even though Columnstore Indexes do not create pre-aggregates, like in SSAS multi-dimensional (MOLAP), under the right circumstances they can match or even out preform SSAS multi-dimensional. The performance increase is largely to the highly efficient nature of the Columnstore Indexes being processed in “Batch” mode.

Financial Data Warehouse Video

February 21, 2014

Over the last few years that I have been blogging my  Dimensional Modeling Financial Data in SSAS and Dimensional Modeling Financial Data in SSAS pt2 posts have  by far been my most popular blog posts.

Since both of these posts are about how to setup a Financial (General Ledger/Chart of Accounts) Data Warehouse, I decided to create a Financial Data Warehouse Video which not only provides a fuller explanation of how to build a Financial Data Warehouse using the MSSQL toolset but also demonstrates why such a data warehouse is valuable.

Financial Data Warehouse

Out of all the different types of Data Warehouses that I have built in my career, Financial (GL/Chart of Accounts) Data Warehouses  provide the most business value, are the quickest to implement, and have the lowest risk.

FileTable and BULK Insert

January 21, 2014

In my video entitled Data Warehouse 12 ELT and FlatFiles  starting at the about the 10 minute, I demonstrate how to manage flat files with SQL Server FileTables.

While FileTables are great for managing flat files, I  failed to include and example of how to BULK insert data from a FileTable into a regular SQL Server table.

This is a very important step for data warehousing and unfortunately, as of the writing of this post, it is not documented at all! and in fact if you to simply preform a BULK insert of the data from the FileTable into a normal SQL table you will get the following cryptic error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\\Garrett-hp\sql2012\FileTableDB\ETLFileTable_Dir\TestTable.txt” could not be opened. Operating system error code 50(The request is not supported.).

Not very helpful  and there is very little about it on the WWW Sad smile , hence the origin of this post.

There are several work-a-rounds to this issue none of which are perfect however my preferred method is to simply map the FileTable directory to  a drive on the OS and then magically BULK insert works!

see screen shot below of a working example:

image

select * from [dbo].[ETLFileTable];
truncate table  FileTableDB.[dbo].[TestLoadTable];
BULK INSERT FileTableDB.[dbo].[TestLoadTable]
 FROM 'Z:test.txt' /*mapped drive*/
 WITH(CHECK_CONSTRAINTS
       ,CODEPAGE='RAW'
       ,DATAFILETYPE='char'
       ,FIELDTERMINATOR='|'
       ,ROWTERMINATOR='\n');
select * from FileTableDB.[dbo].[TestLoadTable];

Additionally, I would caution against using SSIS just to do the insert, especially if SSIS is installed on another server!?

The FileTable data already in the  SQL Server filegroup on the DW server would have to leave the server, traverse the network, be processed by SSIS, then return over the network again, just to be inserted into a table on the DW!!!

Avoid all that and just do a BULK insert. For those who are interested SSIS in FastMode simply does a BULK insert:

Jamie Thompson: SSIS: Destination Adapter Comparison

“Where OLE DB Destination uses an access mode of OPENROWSET, OLE DB Destination with FastLoad uses OPENROWSET with FastLoad.

FastLoad means that the adapter uses BULK INSERT for the insertion.”

Master Data Services

December 6, 2013

 

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists. An MDM project generally includes an evaluation and restructuring of internal business processes along with the implementation of MDM technology. The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

ELT (ETL) Videos

September 27, 2013

There are not any BLOG posts so far this month because I have been concentrating on producing detailed YouTube videos that discuss some of the most important concepts in Data Warehousing.

the latest series of videos deal with ELT (ETL)

 

 

 

 

 

Performance Tuning SQL Server: Indexing

August 15, 2013

Many times I am asked about how to create indexes on a data warehouse SQL Server Database so that queries execute much faster. Unfortunately, unless they are able to upgrade to SQL Server 2012 in order to use ColumnStore Indexes, there is usually very little that I can do to speed up the queries. This is usually do to the fact that the Data Warehouse Server was not architected for data warehouse workloads to begin with and therefore is physically constrained on its throughput (MB/sec).

So I have created this little demonstration to illustrate the point:

I created a 10 million row fact table based on the good old AdventureWorks DB and setup a very simple scan centric SQL Server data warehouse query:

DBCCdropcleanbuffers;      

SETSTATISTICSIOON;SETSTATISTICSTIMEON;

GO

SELECT[SalesTerritoryKey],sum([SalesAmount])

  FROMTest.dbo.[NoCompression_10m]

   whereSalesTerritoryKey= 1

        orSalesTerritoryKey= 4

        orSalesTerritoryKey= 6

        orSalesTerritoryKey= 7

  groupby[SalesTerritoryKey];

 

With logical reads 208337 that means that 1,627 MB of data was returned from disk to satisfy the query. And magically, if you check the size of the table in SQL Server Management Studio it is also reads 1,627 MB. This is due to the fact that there were no indexes to read from and so the SQL Server had to scan the entire table:

image

Now indexes can be created to reduce the amount of data that needs to be scanned by only scanning the index data.

ifnotexists(select*fromsys.indexeswherename=‘ix_NoCompression_10m_SalesAmount’)

  CREATENONCLUSTEREDINDEX[ix_NoCompression_10m_SalesAmount]ON[dbo].[NoCompression_10m]

([SalesAmount]ASC)

GO

 

 ifnotexists(select*fromsys.indexeswherename=‘ix_NoCompression_10m_SalesTerritoryKey’)

 CREATENONCLUSTEREDINDEX[ix_NoCompression_10m_SalesTerritoryKey]ON[dbo].[NoCompression_10m]

([SalesTerritoryKey]ASC)

Note: The best practice is to actually create a separate index for each fact table column, but just to keep things simple I only created an index for the columns that will be queried.

Now if you run the same query again there the message box reads logical reads 36812 or 287 MB. Creating indexes drops by over 500% in the amount of data required for this query. This reduction in data being read is primarily due to the fact that the data is being read from the indexes, which are much smaller, and therefore less time consuming than read all the data directly from the table. See Index Scans:

image

Index Sizes:

 

[ix_NoCompression_10m_SalesAmount] – 27,176 * 8 / 1024 = 212 MB/sec

[ix_NoCompression_10m_SalesTerritoryKey] – 22,276 * 8 / 1024 = 174 MB/sec

 

However, the limitation to this approach is that the data being returned will never be smaller than indexes that are created on the fact table. Real world fact tables have billions of rows and so those indexes will not all fit into memory therefore the data must be retrieved directly from disk which will effect query performance negatively, especially if the data warehouse server has not been specifically tuned to deliver good throughput (MB/sec).

 

In fact even if query patterns (something which you cannot control) change, the amount of data required by the queries will remain about the same. In the following example I commented out 2 predicate values (SalesTerritory). One would think that now the data required would drop by about ½ as well.

 

  DBCCdropcleanbuffers;      

SETSTATISTICSIOON;SETSTATISTICSTIMEON;GO

SELECT[SalesTerritoryKey],sum([SalesAmount])

  FROMTest.dbo.[NoCompression_10m]

  whereSalesTerritoryKey= 1

        orSalesTerritoryKey= 4

        –or SalesTerritoryKey = 6

        –or SalesTerritoryKey = 7

  groupby[SalesTerritoryKey];

However the logical reads only dropped from 36,812 (287 MB) to 33612 (262 MB) or only about 25 MB less data required by removing ½ of the predicate values. Even if the number of predicates values were too drop to 1, the logical reads are still 30128 or 235 MB. This is due to the fact that no matter how many predicate values are used the index for that predicate still has to be scanned. Remember Data Warehouse queries require a lot of historical data and so are scan centric. While scanning the index might not seem all that bad for this small 10 million row fact table imagine what would happen if you increased the number rows of rows by a factor of 100 or even 1,000 that would be potentially 10s of billions of rows and 100s of GB for the indexes!

 

Some of you might be tempted to say that an index could be created for each predicate value (SalesTerritory) while that is true it would be very quickly become unmanageable and in most cases it is nearly impossible to predict all the ways that the users will query the data for historical analysis.

 

The real answer is to correctly architect the data warehouse server ahead of time in order to deliver the query results in a predictable and acceptable period of time. However, with that said there are a few things that you can do to increase the ability data warehouse server to deliver higher MB/second than it normal could under normal conditions.

 

Page Compression works by cramming more data on to each database page which means that fewer pages need to be read from disk.

 

ALTERINDEX[ix_NoCompression_10m_SalesAmount]ON[dbo].[NoCompression_10m]

       REBUILDPARTITION=ALLWITH (DATA_COMPRESSION=PAGE)

 

  ALTERINDEX[ix_NoCompression_10m_SalesTerritoryKey]ON[dbo].[NoCompression_10m]

       REBUILDPARTITION=ALLWITH (DATA_COMPRESSION=PAGE)

 

Note the indexes new sizes:

 

ix_NoCompression_10m_SalesAmount 14279 = 11 MB

ix_NoCompression_10m_SalesTerritoryKey 14350 = 11 MB

 

Due to using Page Compression the indexes are significantly smaller and so it is no surprise that if the same query is run again even less data needs to be scanned from the indexes:

 

Logical Reads = 20475 or 159 MB

 

Page compression reduces the data required from 287 MB to 159 MB or a saving of about 55%. In the real world I see about a 33% savings.

 

And just in case you were wondering, if you upgrade to SQL Server 2012 Columnstore Indexes the data required drops from 287 MB to an incredible 1.06 MB !!!

 

Conclusion:

 

Complex indexing strategies for Data Warehouse workloads provide no additional benefit. The Data Warehouse Server needs to be carefully architected upfront to deliver the required MB/second throughput so that the users are happy with the performance of their queries.

 

Performance Tuning SQL Server: Hardware Architectures Throughput Analysis

July 21, 2013

Over the last few years I have performed many SQL Server Data Warehouse Database Performance Tuning Sessions with a variety of customers in many different environments. This sessions usually consist of a weeklong engagement in which I lead an in-depth software (SQL Server) and hardware tuning classes. The results of which are put into a highly detailed roadmap and list recommendations for improved SQL Server Data Warehouse performance.

As part of this testing, I use SQLIO to benchmark the hardware performance capabilities of the SQL Server Data Warehouse database server that I am testing (see: Data Warehouse Storage Throughput (MB/sec) ). There are of course many other types of tests and analyses that I preform as well as part of this week long process but I am going to stick with SQLIO for now.

Since I have performed these tests many times I would like to share some of the results that I have seen from various customer SQL Server Data Warehouse server environments.

Note: Even though these are actual aggregate figures from actual customers, given the complexity of each environment, your results may vary. This is meant to give you an idea what kind of performance to expect.

QSDW: QuickStart Data Warehouse 1000, This is a Dell Microsoft SQL Data Warehouse Appliance that is built to Microsoft FastTrack Reference Architecture Best Practices such as parallelism.

DAS – SSD: Direct Attach Storage with Solid State Drives. SSDs not architected for Data Warehouse specific workloads.

SAN – SSD/HDD: Tier 1 SAN that uses a combination of Solid State Drives (SSD) and Hard Disk Drives (HDD). Not architected for Data Warehouse specific workloads.

SAN – HDD: Tier 2 SAN that uses only Hard Disks Drives (HDD). Not architected for Data Warehouse specific workloads.

Laptop: My personal Laptop with one 7.2 K HDD

image

As can be determined from the graph the Dell QuickStart Data Warehouse out preforms even Solid
State Devices and both types of SANs. This due primarily to the fact that the QSDW is specifically tuned to data warehouse workloads which are typically characterized by sequential read/writes in block sizes greater than 64KB. In fact had I increased the block sizes for the tests, the gaps in performance would have increased.

Conclusion:

Ensuring that the Data Warehouse server is specifically tuned for Data Warehouse workloads can have a dramatic impact on performance. In fact this tuning can even be more import than the type of storage technology selected e.g. SAN, SSD, or HDD.

Note: The above graph shows server hardware performance not SQL Server Database Engine performance. The tool SQLIO has nothing to do with SQL Server even though the name might suggest otherwise

Data Warehouse on YouTube

July 12, 2013

I am experimenting with creating a series of Data Warehousing videos. Let me know if these videos are helpful and if you would like to see more.

The videos are loosely based on my PASS and SQL Saturday presentations but go into more detail.

Performance Tuning SQL Server: Parallelism

July 7, 2013

One of the most important architectural principles built into Dell’s latest generation of data warehouse appliances called Quickstart Data Warehouse (QSDW)is parallelism. The following post will give an over of how parallelism can be implement using QSDW as a reference.

Breaking a large data warehouse workload into smaller pieces that then can be worked on in parallel is one of the best ways of scaling SQL Server. This parallel processing technique is exemplified in Non-Uniform Memory Access (NUMA).

Hardware and Software Parallelism

Even though the NUMA architecture is used to create parallelism between main memory (RAM) the server’s CPU cores, the same principle can be applied to the entire SQL Server Data Warehouse Server as well.

However this parallelism is not present in most OLTP systems because those systems rely on a few LUNs/Disk Groups with many disks each to achieve maximum IOPs. While this approach is good for OLTP systems it is not the proper architecture for a SQL Server Data Warehouse Server which seeks to maximize MB/second not IOPS.

The best approach, therefore, is break up the disks into several separate Disk Groups or LUNs and place a SQL Server database file on each LUN. When this is done the SQL Server Database Engine can break the read and write operations into smaller chunks according to the number LUNs that were created thus achieving parallelism. The image below shows have 9 LUNs can be created and have a SQL Server Database file put on each LUN.

image

Because the data for the entire Filegroup/Database is broken  out to to multiple LUNs by placing a SQL Server database file on each LUN, all database operations will be parallelized. This has the added effect of increasing MB/sec by a factor roughly equal to the number of LUNs, in this case eight.

This architecture does create parallelism a the disk/database level, however it is not yet aligned to the number CPU cores present on the Data Warehouse Server. To accomplish this next level of parallelism, instead of creating one Filegroup for the data base, the number of Filegroups will have to align to the number of CPU cores.

The illustration below demonstrates what the configuration may look like on a server with eight cores.

image

Physically each LUN is implemented on Windows server as a Mounted Volume and each mounted volume will have eight database files, one database file for each of the eight file groups.

clip_image005

Only now can the SQL Server Data Warehouse Workload be parallelized across the entire server, from disks to the each CPU core. While this approach might seem like a lot of work, the performance benefits are tremendous and lead to much more reliable and consistent performance under a wide variety of data warehouse workload scenarios.

The parallelism techniques described above are built directly into Dell Quickstart Data Warehouse Appliance (QSDW), thus making it a highly tuned SQL Server Data Warehouse solution that can save a customer significant time and money.

DQS Composite Domains

June 16, 2013

Update:

I  felt a little too ambitious and actually recorded a series of 3 videos at MSBI Academy which cover all the content of this post. The videos also give a little bit more back ground information about DQS for those who have not used it yet.

1. Creating a new Data Quality Services (DQS) Domain

2. Adding Business Rules to a Data Quality Services (DQS) Domain

3. Enriching data using a Data Quality Services Project

Introduction:

Data Quality Services (DQS) is a great addition to SQL Server 2012. There were many times in the past were I had wished there was a tool which allowed Data Stewards to clean , validate, and preform matching or de-duplication on the data before it went into the Data Warehouse. In my opinion there are only two kinds of companies: those that don’t recognize that they have data quality issues and those that realize that they have data quality issues! Unfortunately, most organizations don’t find out that they have data quality issues until they attempt a data warehouse project and then those issues become all too apparent.

I am not going to go into all the functional details of DQS. (See above link for great training materials on the DQS team website) However, I want to focus on one of the most important features of DQS that seems to be misunderstood.

The ability to create Composite Domains for data correction or validation is one of the most critical features of DQS and would be required in virtually any DQS solution. However, creating Composite Domain rules in the DQS client UI can be a little tricky if you don’t know what to look for. (At least it was for me hence the reason for this post)

The following example uses the AdventureWorks Sample that is available on the DQS Team website. I suggest that you watch their training videos so that you have a good understanding of what I will describe in the next section.

Scenario:

You want to create Composite Domain rules that populates Title based on the values in Marital Status and Gender such that;

when Gender = ‘M’ male

- then Title = ‘Mr.’, or

when Marital Status = ‘S’ single and Gender = ‘F’ female

- then Title = ‘Ms.’ , or

Marital Status = ‘M’ single and Gender = ‘F’ male

- then Title = ‘Mrs.’

clip_image001

To accomplish this first you need to create all the individual domains and then create the ‘Title Enrichment’ Composite Domain. Next the Tile Enrichment Composite Domain needs a few rules to correctly populate the Title domain/column.

For the Single Female Title rule the first part of the rule or the test condition needs to be created. This is similar to the “IF” of the “IF…THEN…” syntax.

In the following example shows: if Marital Status = ‘S’ then Title = ‘Ms.’

clip_image003

Everything looks good at this point. Now all that is needed is to add an additional condition to the ‘IF’ to test when Gender = ‘F’. However, when you click on the “Adds new condition to the selected clause” button, a new clause is created for the Marital Status domain.

clip_image005

This not what is needed because the Gender domain needs to be used. It would appear that DQS is missing the very important ability to have multiple domains be tested in the same Composite Domain Rule.

The key to creating this type of Composite Domain Rule is to ensure that that no part of the current condition (“Martial Status Value is equal to S”) is in focus when you click on the “Add new condition to the selected clause”. You will know when the current condition is in focus because the vertical line will be solid blue or a blue box will around a part of the condition. See below:

clip_image007

What is required is to; 1. Click away for the part of the current condition before 2. You click on the “Add new condition to the selected clause”

clip_image008

If you do that then you can create the required condition using the Gender domain

clip_image009

Now when a project is run with this Composite Domain and the data in the Title column will have the correct values:

clip_image011


Follow

Get every new post delivered to your Inbox.

Join 38 other followers