Great BLOG posts

I don’t usually do this but there have been recent BLOG posts that due to the importance the information being shared, I would like to call attention too and preserve their conclusions on my BLOG as well.

Thomas Kesjer has created a great series of posts explaining some of the fundamentals of Columnstore Indexes. I particular enjoy the his latest post entitled “What is the Best Sort Order for a Column Store?” because it gives some SQL queries that can be used to calculate such things as data entropy.

example:

p(xi)

SELECT X
, CAST(COUNT(*) AS FLOAT) / SUM(COUNT(*)) OVER (PARTITION BY NULL) pX
FROM Table
GROUP BY X

 H(X)

SELECT -1 * SUM(pX * LOG(pX, 2)) AS H
FROM
(
SELECT X
, CAST(COUNT(*) AS FLOAT) / SUM(COUNT(*)) OVER (PARTITION BY NULL) pX
FROM Table
GROUP BY X
) AS p

The Data Warehouse Junkie also wrote a post entitled “Design SSIS for Performance and Scale – Baseline tests” which demonstrates some good and proven conclusions based on the Fast Track recommendations.

SSIS Degrees of parallelism impact data fragmentation: “the DOP setting has no influence on our SSIS performance and this also means that the BULK API is single threaded (because performance does not increase). As you can also see we don’t get any fragmentation of our data when we increase the DOP setting.”

This is very good to know.

I also think that his conclusion about enabling page compression matches what I have seen with the Fast Track systems that I have worked with as well. Enabling Page Compression comes at the cost of increasing load times but gaining better I/O throughput (sequential reads/writes in MB/sec).  For most DW systems this is a good trade off but if your DW  is not I/O and you have a very tight load window then you might to consider not using Page Compression.

In either case the most important thing to due is to test it first before you make any decisions!

Advertisements

Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: