Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

We have moved!

Thanks for visiting! This blog has now been migrated to:...

Author: Pam Lahoud Date: 03/29/2019

More on Implicit Conversions

Yesterday, a reader posted a question asking me to comment on SQL Server's algorithm for choosing...

Author: Craig Freedman Date: 01/20/2010

Correction to my prior post on sys.dm_db_index_operational_stats

In this post about the sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats DMVs, I...

Author: Craig Freedman Date: 07/29/2009

Maximum Row Size and Query Hints

In my last post (yes, that was two months ago), I gave an example of how a query hint could cause a...

Author: Craig Freedman Date: 06/24/2009

Implied Predicates and Query Hints

In this post, I want to take a look at how two seemingly unrelated features of SQL Server can...

Author: Craig Freedman Date: 04/28/2009

OPTIMIZED Nested Loops Joins

In my past two posts, I explained how SQL Server may add a sort to the outer side of a nested loops...

Author: Craig Freedman Date: 03/18/2009

Optimizing I/O Performance by Sorting – Part 2

In my last post, I discussed how SQL Server can use sorts to transform random I/Os into sequential...

Author: Craig Freedman Date: 03/04/2009

Optimizing I/O Performance by Sorting – Part 1

In this post from last year, I discussed how random I/Os are slower than sequential I/Os...

Author: Craig Freedman Date: 02/25/2009

What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats -...

Author: Craig Freedman Date: 10/30/2008

Random Prefetching

In my last post, I explained the importance of asynchronous I/O and described how SQL Server uses...

Author: Craig Freedman Date: 10/07/2008

Sequential Read Ahead

Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize...

Author: Craig Freedman Date: 09/23/2008

Dynamic Partition Elimination Performance

In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient...

Author: Craig Freedman Date: 08/22/2008

Partitioned Indexes in SQL Server 2008

In my last post, I looked at how SQL Server 2008 handles scans on partitioned tables. I explained...

Author: Craig Freedman Date: 08/05/2008

Partitioned Tables in SQL Server 2008

In this post, I introduced how SQL Server 2005 implements query plans on partitioned tables. If...

Author: Craig Freedman Date: 07/15/2008

Subqueries in BETWEEN and CASE Statements

Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT)CREATE TABLE T2 (A INT, B INT)...

Author: Craig Freedman Date: 06/27/2008

Implicit Conversions

In my last couple of posts, I wrote about how explicit conversions can lead to errors. In this post,...

Author: Craig Freedman Date: 06/05/2008

Query Processing Presentation

Last week, I had the opportunity to talk to the New England SQL Server Users Group. I would like to...

Author: Craig Freedman Date: 05/15/2008

Conversion and Arithmetic Errors: Change between SQL Server 2000 and 2005

In this post from last week, I gave an example of a query with a conversion where the optimizer...

Author: Craig Freedman Date: 05/06/2008

Conversion and Arithmetic Errors

Let's take a look at a simple query: CREATE TABLE T1 (A INT, B CHAR(8))INSERT T1 VALUES (0,...

Author: Craig Freedman Date: 04/28/2008

Ranking Functions: RANK, DENSE_RANK, and NTILE

In my previous post, I discussed the ROW_NUMBER ranking function which was introduced in SQL Server...

Author: Craig Freedman Date: 03/31/2008

Ranking Functions: ROW_NUMBER

SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are...

Author: Craig Freedman Date: 03/19/2008

Halloween Protection

In a prior post, I introduced the notion that update plans consist of two parts: a read cursor that...

Author: Craig Freedman Date: 02/27/2008

Maintaining Unique Indexes with IGNORE_DUP_KEY

A few months ago, I wrote a post describing how SQL Server maintains unique indexes while avoiding...

Author: Craig Freedman Date: 01/30/2008

Partial Aggregation

In some of my past posts, I've discussed how SQL Server implements aggregation including the stream...

Author: Craig Freedman Date: 01/18/2008

Recursive CTEs continued ...

In this post, I will finish the discussion of recursive CTEs that I began in my last post. I will...

Author: Craig Freedman Date: 11/07/2007

Recursive CTEs

One of the most important uses of CTEs is to write recursive queries. In fact, CTEs provide the only...

Author: Craig Freedman Date: 10/25/2007

CTEs (Common Table Expressions)

CTEs or common table expressions, which are new in SQL Server 2005, provide an easy way to break a...

Author: Craig Freedman Date: 10/18/2007

GROUPING SETS in SQL Server 2008

In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE. SQL Server 2008 continues...

Author: Craig Freedman Date: 10/11/2007

Aggregation WITH CUBE

In my last post, I wrote about how aggregation WITH ROLLUP works. In this post, I will discuss how...

Author: Craig Freedman Date: 09/27/2007

Aggregation WITH ROLLUP

In this post, I'm going to discuss how aggregation WITH ROLLUP works. The WITH ROLLUP clause permits...

Author: Craig Freedman Date: 09/21/2007

Maintaining Unique Indexes

Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT)CREATE INDEX TA ON...

Author: Craig Freedman Date: 09/06/2007

Optimized Non-clustered Index Maintenance in Per-Index Plans

In my last post, I showed how SQL Server 2005 only updates non-clustered indexes when the data in...

Author: Craig Freedman Date: 08/22/2007

Optimized Non-clustered Index Maintenance

Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the...

Author: Craig Freedman Date: 08/15/2007

More on TOP

Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP. This week I'll...

Author: Craig Freedman Date: 08/01/2007

ROWCOUNT Top

If you've looked at any insert, update, or delete plans, including those used in some of my posts,...

Author: Craig Freedman Date: 07/25/2007

The UNPIVOT Operator

The UNPIVOT operator is the opposite of the PIVOT operator. As I explained in my earlier post, the...

Author: Craig Freedman Date: 07/17/2007

PIVOT Query Plans

In my last post, I gave an overview of the PIVOT operator. In this post, I'm going to take a look at...

Author: Craig Freedman Date: 07/09/2007

The PIVOT Operator

In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT...

Author: Craig Freedman Date: 07/03/2007

Query Failure with Read Uncommitted

Over the past month or so, I've looked at pretty much every isolation level except for read...

Author: Craig Freedman Date: 06/12/2007

Read Committed and Bookmark Lookup

In my last two posts, I discussed two scenarios - one involving updates and another involving large...

Author: Craig Freedman Date: 06/07/2007

Read Committed and Large Objects

In my last post, I explained that SQL Server holds read committed locks until the end of an update...

Author: Craig Freedman Date: 05/31/2007

Read Committed and Updates

Let's try an experiment. Begin by creating the following simple schema: create table t1 (a int, b...

Author: Craig Freedman Date: 05/22/2007

Serializable vs. Snapshot Isolation Level

Both the serializable and snapshot isolation levels provide a read consistent view of the database...

Author: Craig Freedman Date: 05/16/2007

Repeatable Read Isolation Level

In my last two posts, I showed how queries running at read committed isolation level may generate...

Author: Craig Freedman Date: 05/09/2007

Query Plans and Read Committed Isolation Level

Last week I looked at how concurrent updates may cause a scan running at read committed isolation...

Author: Craig Freedman Date: 05/02/2007

Read Committed Isolation Level

SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read...

Author: Craig Freedman Date: 04/25/2007

Parallel Query Execution Presentation

For those of you readers who've been wondering whatever happened to me, I've been rather busy. Among...

Author: Craig Freedman Date: 04/17/2007

Semi-join Transformation

In several of my prior posts, I’ve given examples of semi-joins. Recall that semi-joins essentially...

Author: Craig Freedman Date: 12/04/2006

Introduction to Partitioned Tables

In this post, I’m going to take a look at how query plans involving partitioned tables work. Note...

Author: Craig Freedman Date: 11/27/2006

Parallel Hash Join

SQL Server uses one of two different strategies to parallelize a hash join. The more common strategy...

Author: Craig Freedman Date: 11/16/2006

Next>