Craig Freedman's SQL Server Blog

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

Browse by Tags

Tagged Content List
  • Blog Post: Random Prefetching

    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...
  • Blog Post: 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...
  • Blog Post: Halloween Protection

    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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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,...
  • Blog Post: ROWCOUNT Top

    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...
  • Blog Post: 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...
Page 1 of 1 (9 items)