I have shown how you can find out if SQL Server eliminates partitions in your queries. Most of the time SQL Server is doing great job in partition elimination and it is scanning or seeking only partitions that could potentially yield rows satisfying the WHERE, IN or other row eliminating clauses. In spite of that you should still check the query plans of your queries against partitioned tables if you suspect the performance may be or is an issue. You should be aware that SQL Server is employing both the static partition elimination when the query is optimized and dynamic partition elimination when the choice of scanned and skipped partitions is made at the query execution time. ...

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...

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...

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...

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...

It is important to understand whether CPU pressure is affecting SQL Server performance. This is true even in the case where SQL Server is the only application running on a particular box. The System object Perfmon counter Processor Queue length is not...

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...

SQLCAT Blog: Question: How can SQL Server 2005 help me evaluate and manage indexes? (1) How can I find out whether my indexes are useful? How are they used? (2) Do I have any tables or indexes that are not used (or rarely) (3) What is the...

Question: How can SQL Server 2005 help me evaluate and manage indexes? (1) How can I find out whether my indexes are useful? How are they used? (2) Do I have any tables or indexes that are not used (or rarely) (3) What is the cost of index maintenance...

It's a new year and, with it, we come bearing gifts! We have a pretty significant update to SQL Server 2005 Best Practices Analyzer. It contains many new and updated rules for Analysis Services, a few important rules for the Relational Engine, a couple bug fixes for the UI and command line tools. And all of these rules have rich documentation telling you what need to know. ......

Backup Compression in SQL2008 performs very well and it is easy to use. You could either use “With Compression” along with the “Backup” command or simply enable default backup compression through sp_configure by setting ‘backup compression default’ value...

Q: When querying SS2005 Partitioned Tables, what kind of parallelism should I expect to see and how will that affect performance? A: First, a brief background on partitioning. SQL Server 2005 table partitioning provides many improvements in terms...

SQLCAT Blog: Managing Schema Changes (Part 2) Question: What are best practices for managing schema changes in SQL Server 2005? What are the fastest, least intrusive techniques for high availability? Answer: In Part 1, we outlined the behavior...

I received a question from one of our customers about using the NOLOCK hint: can it cause missing rows in scans even if the rows were committed well before my SELECT with NOLOCK starts? The NOLOCK hint is employed by many users to avoid contention...

In data warehouses, it is quite common that fact records arrive with a source system key that has not yet been loaded in the dimension tables. This phenomena is known as “late arriving dimensions” or “early arriving facts” in Kimball terminology. When...

Author: Sunil Agarwal Reviewers: 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...

Many applications need to generate sequentially incremental number as unique/primary key of records. This blog describes additional options of generating sequence numbers beyond the SQL Server's support of identity column....

In workloads that involve expensive long-running queries and reports, or complex batch operations, you frequently want to see the SQL statements and associated query plans that are active on the system or that consume the most resources. SQL Server...

Hello SQL Server Community! My name is Mark Souza and I work on the SQL Server product team and manage all customer programs. One exciting team that I manage is called the SQL Server Customer Advisory team (SQLCAT). This team spends 75% of their time...

Prem Mehra and Mike Ruthruff We recommend that prior to deploying SAN in a SQL Server production environment you conduct a bench-mark to establish a performance base-line. There are several benefits: 1) Establish a base-line so that you know ahead...

Performance and Tuning Blue Prints We will look at different types of applications, how they use resources, and how one would approach performance tuning each. The performance profile of OLTP differs significantly from a Relational Data Warehouse or...

OVERVIEW: One of the common complaints in using database snapshots is how to get queries and reports to switch over and start using the new snapshot as soon as it is available. There are three basic ways to accomplish this: 1. Delete the old and create...

One of the presenters at recent SQL Server Open World conference in Denmark ( http://www.miracleas.dk/index.asp?page=168&page2=323 ) suggested tuning SQL queries using logical IOs to measure quality of query plan. The IO count is returned for each...

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...

Author : Shaun Tinline-Jones Reviewers : Mike Ruthruff, Sanjay Mishra, Alexei Khalyako Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively...