Performance impact of memory grants on data loads into Columnstore tables

Reviewed by: Dimitri Furman, Sanjay Mishra, Mike Weiner, Arvind Shyamsundar, Kun Cheng, Suresh Kandoth, John Hoang Background Some of the best practices when bulk inserting into a clustered Columnstore table are: Specifying a batch size close to 1048576 rows, or at least greater than 102400 rows, so that they land into compressed row groups directly…. Read more

Azure SQL Data Warehouse loading patterns and strategies

Authors: John Hoang, Joe Sack and Martin Lee Abstract This article provides an overview of the Microsoft Azure SQL Data Warehouse architecture. This platform-as-a service (PaaS) offering provides independent compute and storage scaling on demand. This document provides data loading guidelines for SQL Data Warehouse. Several common loading options are described, such as SSIS, BCP,… Read more

Improve query performance on memory optimized tables with Temporal using new index creation enhancement in SP1

Reviewed by: Dimitri Furman,Sanjay Mishra, Mike Weiner With the introduction of the Temporal feature in SQL 2016 and Azure SQL Database, there is an ability to time travel through the state of data as it was at any given point of time. Alongside In-Memory OLTP, Temporal on memory optimized tables allows you to harness the… Read more

SQL Server 2016 SSIS Data Flow Buffer Auto Sizing capability benefits data loading on Clustered Columnstore tables

  Reviewers: Joe Sack, Arvind Shyamsundar, Murshed Zaman, Sanjay Mishra  The focus of this blog is to introduce SSIS changes in the Data Flow task in SQL Server 2016 that help data loading into Columnstore tables. On SQL Server relational data warehouses, Columnstore indexes are being widely used due to the immense value realized by… Read more

Choosing hash distributed table vs. round-robin distributed table in Azure SQL DW Service

This topic explains the various Azure SQL Data Warehouse distributed table types, and offers guidance for choosing the type of distributed table to use and when. There are two types of distributed tables in Azure SQL DW at the writing of this article, hash distributed table and round-robin distributed table. Designing databases to use these… Read more

Data Loading performance considerations with Clustered Columnstore indexes

This article describes data loading strategies specific to tables with a Clustered Columnstore index on SQL Server 2014.  For fundamental data  loading strategies, an excellent read is the whitepaper Data Loading Performance Guide and it is greatly recommended. Though that whitepaper doesn’t include Columnstore indexes, many other concepts presented there still hold true for any data… Read more

SQLCAT.Com – eBook Downloads

As of September 1, 2013 we decided to remove SQLCAT.COM site and use MSDN as the primary vehicle to post new SQL Server content. This was done to minimize reader confusion and to streamline content publication.  MSDN SQLCAT blogs already includes most SQLCAT.COM Content and will continue to be updated with more SQLCAT learnings.  You can also… Read more

SQLCAT.com consolidated index of Whitepapers

In an effort to migrate as much content as possible from SQLCAT.com to the MSDN SQL CAT site we have consolidated all of the Whitepapers published over the last few years.   While all of the content actually exists on Technet and MSDN download sites, SQLCAT was a great resource to start looking.  Hopefully this will… Read more

New version of Partition Management Utility supports SQL2012, ColumnStore Indexes

A new version of the Partition Management Utility for SQL Server is now available on Codeplex at http://sqlpartitionmgmt.codeplex.com/.   This is a tool that helps you create necessary staging tables and indexes and associated check constraints, to support partition-switch operations against existing partitioned tables.   This solves the problem of keeping staging table scripts in synch when… Read more

Technical Reference Guides for Designing Mission-Critical Solutions using SQL Server are available

The Technical Reference Guides for Designing Mission-Critical Solutions provide planning and architecture guidance for various mission-critical workloads deployed by users. These guidesreflect the knowledge gained by the CAT team while working with customers onmission-critical deployments, many of these deployments being the result ofdeep customer engagements by the CAT organization. Each guide provides not onlythe key… Read more