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

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

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

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

AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.

Author: Sanjay Mishra Reviewers: David P. Smith (Active Network), Mike Ruthruff (Bungie Studios), Matt Neerincx, Luis Carlos Vargas Herring, Piyush Ranjan, Steven Schneider   My job provides me opportunity to work across different sections of customers – some customers who like all defaults (default instances, default port numbers, etc.), and some customers who don’t like… Read more

Distributed Partitioned Views / Federated Databases: Lessons Learned

Introduction This article contains information about the things we have learned while working with Federated Databases.  Before beginning it is necessary to define the terms being used.  Included in this article is one solution in production that is using federated servers and Distributed Partitioned Views.   Definitions Definition 1:  Local Partitioned View – A single… Read more

How to warm up the Analysis Services data cache using Create Cache statement?

Goal This document describes how to build Create Cache commands.  Create Cache for Analysis Services (AS) was introduced in SP2 of SQL Server 2005. It can be used to make one or more queries run faster by populating the OLAP storage engine cache first.   Some customers have found certain queries benefit other later queries. … Read more

Linked Servers to SQL Azure

Authors:  Kevin Cox & Michael Thomassy Contributors: Lubor Kollar Technical Reviewers: Shaun Tinline-Jones, Chuck Heinzelman, Steve Howard, Kun Cheng, Jimmy May Overview   Connecting directly to a SQL Azure database from a reporting tool (like Microsoft Excel and PowerPivot, or SQL Server Reporting Services) from your desktop or local data center is possible using a… Read more