Craig Freedman's SQL Server Blog
A discussion of query processing, query execution, and query plans in SQL Server.
Translate This Page
Translate this page
Common Table Expressions
Pivot and Unpivot
Rollup and Cube
Scans and Seeks
Browse by Tags
Craig Freedman's SQL Server Blog
Tagged Content List
In my last post , I explained the importance of asynchronous I/O and described how SQL Server uses sequential read ahead to boost the performance of scans. In this post, I'll discuss how SQL Server uses random prefetching. Let's begin with a simple example of a query plan that performs many random I...
7 Oct 2008
Query Processing Presentation
Last week, I had the opportunity to talk to the New England SQL Server Users Group . I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it. My talk was an introduction to query processing, query execution, and query plans in SQL Server...
15 May 2008
In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that identifies the rows to be updated and a write cursor that actually performs the updates. Logically speaking, SQL Server must execute the read cursor and write cursor of an update plan in two separate...
27 Feb 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 false uniqueness violations. In this post, I'm going to look at how SQL Server maintains unique indexes that were created with the WITH IGNORE_DUP_KEY clause. Normally, if we attempt to insert a duplicate...
30 Jan 2008
Maintaining Unique Indexes
Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT) CREATE INDEX TA ON T(A) CREATE UNIQUE INDEX TB ON T(B) INSERT T VALUES (0, 0, 0) INSERT T VALUES (1, 1, 1) Now suppose we run the following update statement: UPDATE T SET A = 1 - A This update statement affects...
6 Sep 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 the index actually changes. For my example, I used a simple update statement that results in a per-row or narrow plan. In this post, I'll show how this optimization works in a per-index or wide update plan...
22 Aug 2007
Optimized Non-clustered Index Maintenance
Insert, update, and delete plans consist of two parts. The first part or read cursor identifies the list of rows to be inserted, update, or deleted. The second part or write cursor performs the actual insert, update, or delete. Let's look at a simple example: CREATE TABLE T (PK INT, A INT, B INT,...
15 Aug 2007
If you've looked at any insert, update, or delete plans, including those used in some of my posts, you've probably noticed that nearly all such plans include a top operator. For example, the following update statement yields the following plan: CREATE TABLE T (A INT) INSERT T VALUES (0) INSERT T VALUES...
25 Jul 2007
Read Committed and Updates
Let's try an experiment. Begin by creating the following simple schema: create table t1 (a int, b int) create clustered index t1a on t1(a) insert t1 values (1, 1) insert t1 values (2, 2) insert t1 values (3, 3) create table t2 (a int) insert t2 values (9) In session 1, lock the third row...
22 May 2007
Page 1 of 1 (9 items)
© 2014 Microsoft Corporation.
Privacy & Cookies