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:...
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...
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...
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...
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...
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...
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...
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...
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 -...
Date: 10/30/2008
Random Prefetching
In my last post, I explained the importance of asynchronous I/O and described how SQL Server uses...
Date: 10/07/2008
Sequential Read Ahead
Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize...
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...
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...
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...
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)...
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,...
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...
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...
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,...
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...
Date: 03/31/2008
Ranking Functions: ROW_NUMBER
SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are...
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...
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...
Date: 01/30/2008
Partial Aggregation
In some of my past posts, I've discussed how SQL Server implements aggregation including the stream...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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,...
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...
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...
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...
Date: 07/03/2007
Read Committed and Bookmark Lookup
In my last two posts, I discussed two scenarios - one involving updates and another involving large...
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...
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...
Date: 05/22/2007
Serializable vs. Snapshot Isolation Level
Both the serializable and snapshot isolation levels provide a read consistent view of the database...
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...
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...
Date: 05/02/2007
Read Committed Isolation Level
SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read...
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...
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...
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...
Date: 11/27/2006
Parallel Hash Join
SQL Server uses one of two different strategies to parallelize a hash join. The more common strategy...
Date: 11/16/2006