Ask Learn
Preview
Please sign in to use this experience.
Sign inNote
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
A discussion of query processing, query execution, and query plans in SQL Server.
Thanks for visiting! This blog has now been migrated to:...
Date: 03/29/2019
Yesterday, a reader posted a question asking me to comment on SQL Server's algorithm for choosing...
Date: 01/20/2010
In this post about the sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats DMVs, I...
Date: 07/29/2009
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
In this post, I want to take a look at how two seemingly unrelated features of SQL Server can...
Date: 04/28/2009
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
In my last post, I discussed how SQL Server can use sorts to transform random I/Os into sequential...
Date: 03/04/2009
In this post from last year, I discussed how random I/Os are slower than sequential I/Os...
Date: 02/25/2009
SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats -...
Date: 10/30/2008
In my last post, I explained the importance of asynchronous I/O and described how SQL Server uses...
Date: 10/07/2008
Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize...
Date: 09/23/2008
In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient...
Date: 08/22/2008
In my last post, I looked at how SQL Server 2008 handles scans on partitioned tables. I explained...
Date: 08/05/2008
In this post, I introduced how SQL Server 2005 implements query plans on partitioned tables. If...
Date: 07/15/2008
Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT)CREATE TABLE T2 (A INT, B INT)...
Date: 06/27/2008
In my last couple of posts, I wrote about how explicit conversions can lead to errors. In this post,...
Date: 06/05/2008
Last week, I had the opportunity to talk to the New England SQL Server Users Group. I would like to...
Date: 05/15/2008
In this post from last week, I gave an example of a query with a conversion where the optimizer...
Date: 05/06/2008
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
In my previous post, I discussed the ROW_NUMBER ranking function which was introduced in SQL Server...
Date: 03/31/2008
SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are...
Date: 03/19/2008
In a prior post, I introduced the notion that update plans consist of two parts: a read cursor that...
Date: 02/27/2008
A few months ago, I wrote a post describing how SQL Server maintains unique indexes while avoiding...
Date: 01/30/2008
In some of my past posts, I've discussed how SQL Server implements aggregation including the stream...
Date: 01/18/2008
In this post, I will finish the discussion of recursive CTEs that I began in my last post. I will...
Date: 11/07/2007
One of the most important uses of CTEs is to write recursive queries. In fact, CTEs provide the only...
Date: 10/25/2007
CTEs or common table expressions, which are new in SQL Server 2005, provide an easy way to break a...
Date: 10/18/2007
In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE. SQL Server 2008 continues...
Date: 10/11/2007
In my last post, I wrote about how aggregation WITH ROLLUP works. In this post, I will discuss how...
Date: 09/27/2007
In this post, I'm going to discuss how aggregation WITH ROLLUP works. The WITH ROLLUP clause permits...
Date: 09/21/2007
Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT)CREATE INDEX TA ON...
Date: 09/06/2007
In my last post, I showed how SQL Server 2005 only updates non-clustered indexes when the data in...
Date: 08/22/2007
Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the...
Date: 08/15/2007
Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP. This week I'll...
Date: 08/01/2007
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 is the opposite of the PIVOT operator. As I explained in my earlier post, the...
Date: 07/17/2007
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
In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT...
Date: 07/03/2007
In my last two posts, I discussed two scenarios - one involving updates and another involving large...
Date: 06/07/2007
In my last post, I explained that SQL Server holds read committed locks until the end of an update...
Date: 05/31/2007
Let's try an experiment. Begin by creating the following simple schema: create table t1 (a int, b...
Date: 05/22/2007
Both the serializable and snapshot isolation levels provide a read consistent view of the database...
Date: 05/16/2007
In my last two posts, I showed how queries running at read committed isolation level may generate...
Date: 05/09/2007
Last week I looked at how concurrent updates may cause a scan running at read committed isolation...
Date: 05/02/2007
SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read...
Date: 04/25/2007
For those of you readers who've been wondering whatever happened to me, I've been rather busy. Among...
Date: 04/17/2007
In several of my prior posts, I’ve given examples of semi-joins. Recall that semi-joins essentially...
Date: 12/04/2006
In this post, I’m going to take a look at how query plans involving partitioned tables work. Note...
Date: 11/27/2006
SQL Server uses one of two different strategies to parallelize a hash join. The more common strategy...
Date: 11/16/2006