Developers choice: Columnstore index in Standard and Express Edition with SQL Server 2016 (SP1)

On 16th Nov 2016, Microsoft announced the support for ‘Consistent Programming Surface Area’ across all editions of SQL Server with few exceptions. Please refer to the SQL Server 2016 SP1 release blog post. The key value proposition is that it allows customers to use the same code base independent of the editions in SQL Server.  For example, An ISV…

9

Developers Choice: CREATE OR ALTER

Today we are starting a new blog post series entitled “Developers Choice”. In the first few posts we will be focusing on SQL Server 2016 SP1 enhancements that focus on the developer community, including all developer DBA’s. Following in the same reasoning of simplifying DDL statements that was started by the DROP IF EXISTS language…


New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate…

4

Columnstore Index: Why do I need to create clustered columnstore Index on In-Memory OLTP table for Analytics?

The columnstore index on In-Memory table(s) targets real-time operational analytics (HTAP) allowing customers to eliminate the need for ETL (Extract, Transform and Load) into a separate Data Warehouse.  For details on the scenario, please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/18/real-time-operational-analytics-a-short-video-on-the-value-proposition-in-sql-server-2016/  You can enable HTAP on disk-based tables by creating NCCI on one or more tables as shown in…


In-Memory OLTP: Is your database just in memory or actually optimized for memory?

In my many conversations with customers during Microsoft events, people often confuse between the terms ‘In Memory’ and ‘Memory-Optimized’. Many of us think that they are one and the same. If you continue reading this blog, you will realize that they are somewhat related but can lead to very different performance/scalability. To understand this, let us…

2

Columnstore Index: Which Columnstore Index is right for my workload?

While we all understand that columnstore index in SQL Server 2016 can deliver amazing query performance for analytic queries but how do you choose among multiple flavors of columnstore index available. The SQL Server 2016 offers the following choices Clustered columnstore index (CCI) Clustered columnstore index with one or more nonclustered btree indexes (CCI/NCI) Nonclustered…


Columnstore index: Why do we refer to it as In-Memory Analytics?

Columnstore index is part of In-Memory technologies released in SQL Server 2014 and SQL Server 2016. Please refer to https://blogs.msdn.microsoft.com/sqlserverstorageengine/tag/columnstore-index/ for a series of blogs on columnstore index. As the name In-Memory Analytics implies, you may think that columnstore index is entirely memory resident but it is not really the case for columnstore indexes created on…


Migrating SAP workloads to SQL Server just got 2.5x faster

SAP provides R3load process to perform export/import of SAP data to SQL Server for homogenous (SQL Server to SQL Server) or heterogeneous migration from different OS or database platform (UNIX/Oracle). By default, R3Load process performs parallel bulk load operations while importing the data in SQL Server. With SQL Server 2014 SP1 CU8, SQL Server 2014…


Integrating PolyBase with Cloudera using Active Directory Authentication

This article outlines the steps to use PolyBase in SQL 2016(including R-Services) with a Cloudera Cluster and setup authentication using Active Directory in both SQL 2016 and Cloudera. Prerequisites Cloudera Cluster Active Directory with Domain Controller SQL Server 2016 with PolyBase and R-Services installed NOTE: We have tested the configuration using the Cloudera Cluster 5.5 running on…


Filestream RsFx Driver fails to load after installing SQL Server 2016 with CU2 on Windows Server 2016 with SecureBoot ON

Starting Windows 10 version 1607 (Redstone 1) and Windows Server 2016, there is a new enforcement for kernel drivers, requiring that all kernel drivers must be signed by Windows Hardware Developer Center Dashboard portal (Dev Portal) as documented by Windows hardware certification blog. It means that Windows will not load the kernel drivers that were not…