You can read about sp_spaceused on MSDN here (https://msdn.microsoft.com/en-us/library/ms188776.aspx), but a brief explanation is as follows:
·         Reserved is the total amount of space allocated by objects in the database
·         Data is the total amount of space used by data
·         Index_size is the total amount of space used by index
·         Unused is the total amount of space reserved for objects in the database, but not yet used.

Nicely formatted, our test data looks like this:

This is a fundamental principle of query performance.  You want SQL Server to use ‘Batch’ mode vs ‘Row’ mode as much as possible.  If you look under your Heap or NoCSI scan operators, you will see that they are using a ‘Row’ Actual Execution Mode … and therefore they take longer to execute.

Okay, now let’s add a WHERE clause as 99% of the OLAP queries I have seen want to filter or slice on some sort of date value.  We should also talk about segment elimination, which you can read up from TechNet here --> (http://social.technet.microsoft.com/wiki/contents/articles/5651.understanding-segment-elimination.aspx).

The short version is SQL Server segments columnstore columns in groups of roughly 1 million rows.  Metadata associated with each segment exposes a MAX and MIN value of each segment.  So when you filter on DimDate.DateKey between [x] and [y], SQL Server will look at the segment metadata and automatically skip past those segments that do not contain qualifying column values.  An example of how to verify columnstore segment elimination can be found here --> (http://social.technet.microsoft.com/wiki/contents/articles/5611.verifying-Columnstore-segment-elimination.aspx) but for our purposes, let’s have an element of trust and look at our execution results from a filtered query.

First, let’s take a look to see what we have to work with.

A quick note about our ‘Heap’ table and the ‘NoCSI’ table which contained a clustered primary key.  How many times have you heard the phrase “index lite”?  We have a clustered PK on our FactTransactionsNoCSI and our one-to-one degenerate dimension DimTransactions to support that relationship, but notice how the ‘Heap’ table actually had better query performance.

It is only fair that we take a minute to demo a couple of anti-performance practices given to TechNet’s SQL Server Columnstore Performance Tuning article found here -->  (http://social.technet.microsoft.com/wiki/contents/articles/4995.sql-server-Columnstore-performance-tuning.aspx)
1.       String Filters
2.       OUTER JOINS
3.       Joining to an equally large DegenerateDim table that does not have a columnstore index

Here is our t-SQL where we are just wanting OPEN status transactions
SELECT
        c.cal_Year
       ,i.itm_Code
       ,SUM(trn_NetAmt) as SumOfNetAmt
FROM
       dbo.FactTransactionsWithCCSI t
INNER JOIN dbo.DimCalendar c on t.trn_TransDateSK = c.cal_DateIntPK
INNER JOIN dbo.DimITem i on t.trn_ItemSK = i.itm_PK
INNER JOIN dbo.DimTransactions dt on t.trn_PK = dt.trn_PK
WHERE t.trn_TransDateSK Between 20060101 and 20121231
  and dt.trn_StatusCode <> 'CLOSED'
GROUP BY c.cal_Year, i.itm_Code
ORDER BY i.itm_Code, c.cal_Year

Here is our query execution plan where our filter cost is 86% of our total execution time.  Note the ‘Row’ Actual Execution Mode.

The case can be made that this is expected behavior and not the fault of our fact table columnstore index, and that is correct as we are filtering on DimTransactions which does not have a columnstore.  Let’s put a columnstore index on DimTransaction as it does have as many rows as our fact table, and see what happens.

CREATE TABLE [dbo].[DimTransactionsWithCCSI](
       [trn_PK] [int] NOT NULL,
       [trn_No] [int] NOT NULL,
       [trn_SeqNo] [int] NOT NULL,
       [trn_SourceCode] [varchar](50) NOT NULL,
       [trn_RouteCode] [varchar](50) NULL,
       [trn_StatusCode] [varchar](50) NULL,
) ON [PRIMARY]
GO

insert dbo.DimTransactionsWithCCSI
select * from dbo.DimTransactions

create clustered Columnstore index dtClustColStoreIndex ON dbo.DimTransactionsWithCCSI
go
 
SELECT
        c.cal_Year
       ,i.itm_Code
       ,SUM(trn_NetAmt) as SumOfNetAmt
FROM
       dbo.FactTransactionsWithCCSI t
INNER JOIN dbo.DimCalendar c on t.trn_TransDateSK = c.cal_DateIntPK
INNER JOIN dbo.DimITem i on t.trn_ItemSK = i.itm_PK
INNER JOIN dbo.DimTransactionsWithCCSI dt on t.trn_PK = dt.trn_PK
WHERE t.trn_TransDateSK Between 20060101 and 20121231
  and dt.trn_StatusCode <> 'CLOSED'
GROUP BY c.cal_Year, i.itm_Code
ORDER BY i.itm_Code, c.cal_Year

Change the INNER JOINS to LEFT OUTER JOINS.
SELECT
        c.cal_Year
       ,i.itm_Code
       ,SUM(trn_NetAmt) as SumOfNetAmt
FROM
       dbo.FactTransactionsWithCCSI t
LEFT OUTER JOIN dbo.DimCalendar c on t.trn_TransDateSK = c.cal_DateIntPK
LEFT OUTER JOIN dbo.DimITem i on t.trn_ItemSK = i.itm_PK
LEFT OUTER JOIN dbo.DimTransactionsWithCCSI dt on t.trn_PK = dt.trn_PK
WHERE t.trn_TransDateSK Between 20060101 and 20121231
  and dt.trn_StatusCode <> 'CLOSED'
GROUP BY c.cal_Year, i.itm_Code
ORDER BY i.itm_Code, c.cal_Year

 The result in SQL 2014 is that my LEFT OUTER joins still used ‘batch’ Actual Execution Mode and SQL Server is even aware enough to turn the one-to-one relationship between DimTrans and FactTrans into an INNER join.

I will be writing more about SQL Server 2016, so this post is just cover step #1: installation. After downloading from Microsoft (http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/default.aspx), installation was typical.  I only ran into one anomaly, the Oracle JRE.

1.       If you choose to install Polybase Query Service for External Data…

 … MS will be looking for the Java SE Runtime Environment v7 or higher.  You can download it from here --> http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html.  Without the Oracle JRE installed, you will receive this failure message:

            Your SQL 2016 installed instances should look something like this:

Your Variable Values now look something like this.  Identify your 32-bit paths, highlighted here in green.

• c:\oracle\product\11.2.0\client64\bin;c:\oracle\product\11.2.0\client32\bin;C:\Program Files\RA2HP\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\ActivIdentity\ActivClient\;C:\Program Files…
  

   d.      Copy and paste your 32 bit path to sit BEFORE your 64 bit path.  Your Variable Value should now look something like this.  Make sure you don’t leave empty spaces (!).

• c:\oracle\product\11.2.0\client32\bin;c:\oracle\product\11.2.0\client64\bin;C:\Program Files\RA2HP\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\ActivIdentity\ActivClient\;C:\Program Files…
  

   e.       Click <OK> to close your Edit System Variable window and save your changes.

12.       Reboot your PC to have your “Path” environment variable update take effect.


Third, Connecting to Oracle
You can use several tools to connect to Oracle.  Before beginning, verify that your “tnsnames.ora” file (located in [YourOracleHomeDirectory]\product\11.2.0\client_1\network\admin) has the correct information as shown below.

#---------------------
# My Project Name
#Replace the server name below with your Oracle server name
#Replace the IP addresses with your IP address
#------------------------
FSMISt2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 130.175.189.36)(PORT = 1523))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 130.175.189.37)(PORT = 1523))
    )
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DCM2PN)
    )
  )

1.       From Visual Studio 2010 Ultimate, choose Tools --> Connect to Database from the main menu.