Partition Elimination in SQL Server 2005

Lubor Kollar   Partition elimination is very important when SQL Server executes queries against partitioned tables or partitioned views. In general, SQL Server is doing an excellent job of not scanning the partitions that are excluded by some predicates. Recently we have discovered one scenario where the partition elimination does not work against partitioned tables… Read more

PolyBase Setup Errors and Possible Solutions

Blog Authors: Murshed Zaman and Sumin Mohanan Reviewer(s): Barbara Kess Prologue PolyBase is a new feature in SQL Server 2016. It was popularized by APS (Microsoft Analytics Platform System) and Azure SQL DW. PolyBase allows access to relational and non-relational data from SQL Server using familiar T-SQL language. It allows you to run queries on… Read more

Load 1TB in less than 1 hour

OVERVIEW   This project was done using SQL Server 2005 Enterprise Edition (with SP1 beta) using the BULK INSERT command with 60 parallel input files, each of approximately 16.67 GB.  The best time I got was 50 minutes.  Hewlett Packard was kind enough to loan me some time one night on the machine they use… Read more

Concurrency Series: Basics of Transaction Isolation Levels

Author: Sunil AgarwalReviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu In this series of blogs, I want to show some common concurrency questions that we get asked by customers. Before we look into common concurrency issues seen by customers, it will be good to review the transaction isolation levels provided in SQL… Read more

How the SQLCAT Customer Lab is Monitoring SQL on Linux

Reviewed By: Denzil Ribeiro, Dimitri Furman, Mike Weiner, Rajesh Setlem, Murshed Zaman Background SQLCAT often works with early adopter customers, bring them into our lab, and run their workloads. With SQL Server now available on Linux, we needed a way to visualize performance and PerfMon, being a Windows only tool, was no longer an option…. Read more

SQLSweet16!, Episode 3: Parallel INSERT … SELECT

Sanjay Mishra, with Arvind Shyamsundar Reviewed By: Sunil Agarwal, Denzil Ribeiro, Mike Ruthruff, Mike Weiner Loading large amounts of data from one table to another is a common task in many applications. Over the years, there have been several techniques to improve the performance of the data loading operations. SQL Server 2014 allowed parallelism for… 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 briefly described, but the main… Read more

Try and try again: not always a good idea (at least not for SSMS!)

Contributions from, and reviewed by: Ken Van Hyning, David Shiflet, Charles Gagnon and Alan Ren (SSMS dev team), Dimitri Furman, Mike Weiner and Rajesh Setlem (SQLCAT) Background SQL Server Management Studio (SSMS) is the most popular client used to administer and work with SQL Server and Azure SQL DB. Internally, the SSMS code uses the… Read more

Achieving Selective Uniqueness in SQL Server Tables

SQL Server development group is repeatedly getting questions, suggestions and requests related to supporting unique indexes allowing multiple NULLs in SQL Server.   The SQL Standard requires that a column—or a set of columns—which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional… Read more

Deploying SQL Server 2005 with SAN #1

Deploying SQL Server 2005 with SAN #1   Prem Mehra and Mike Ruthruff   An often asked question is how to design and deploy SAN with SQL Server 2005. The question is frequently raised by installations that are either deploying SQL Server for the first time or are upgrading to SAN from direct attach storage…. Read more