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: 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 - that are extremely useful for monitoring which indexes are used as well as how and when they are used. Both DMVs report similar statistics on information such as the number of scans, seeks, and updates...
  • Blog Post: Sequential Read Ahead

    Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize hardware utilization. SQL Server includes two asynchronous I/O mechanisms - sequential read ahead and random prefetching - that are designed to address this challenge. To understand why asynchronous I...
  • Blog Post: Partitioned Indexes in SQL Server 2008

    In my last post , I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that SQL Server 2008 treats partitioned tables as tables with a logical index on the partition id column and that SQL Server 2008 implements partition elimination by performing a logical index seek on the...
  • Blog Post: Partitioned Tables in SQL Server 2008

    In this post , I introduced how SQL Server 2005 implements query plans on partitioned tables. If you've read that post or used partitioned tables, you may recall that SQL Server 2005 uses a constant scan operator to enumerate the list of partition ids that need to be scanned. As a refresher, here is...
  • 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: Repeatable Read Isolation Level

    In my last two posts, I showed how queries running at read committed isolation level may generate unexpected results in the presence of concurrent updates. Many but not all of these results can be avoided by running at repeatable read isolation level. In this post, I'll explore how concurrent updates...
  • Blog Post: Read Committed Isolation Level

    SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read committed, repeatable read, and serializable. SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot. These isolation levels determine what locks SQL Server takes when accessing data...
  • Blog Post: Introduction to Partitioned Tables

    In this post, I’m going to take a look at how query plans involving partitioned tables work. Note that there is a big difference between partitioned tables (available only in SQL Server 2005) and partitioned views (available both in SQL Server 2000 and in SQL Server 2005). I will look at the query plans...
  • Blog Post: Parallel Scan

    In this post, I’m going to take a look at how SQL Server parallelizes scans. The scan operator is one of the few operators that is parallel “aware.” Most operators neither need to know nor care whether they are executing in parallel; the scan is an exception. How does parallel scan work? The threads...
  • Blog Post: Index Union

    I was planning to continue writing about parallelism this week (and I will continue another time in another post), but I received an interesting question and chose to write about it instead. Let’s begin by considering the following query: create table T ( a int , b int , c int , x char ( 200 )...
  • Blog Post: Index Examples and Tradeoffs

    The optimizer must choose an appropriate “access path” to read data from each table referenced in a query. The optimizer considers many factors when deciding which index to use, whether to do a scan or a seek, and whether to do a bookmark lookup. These factors include: How many I/Os will a seek...
  • Blog Post: Seek Predicates

    Before SQL Server can perform an index seek, it must determine whether the keys of the index are suitable for evaluating a predicate in the query. Single column indexes Single column indexes are fairly straightforward. SQL Server can use single column indexes to answer most simple comparisons including...
  • Blog Post: Bookmark Lookup

    In my last post, I explained how SQL Server can use an index to efficiently locate rows that qualify for a predicate. When deciding whether to use an index, SQL Server considers several factors. These factors include checking whether the index covers all of the columns that the query references (for...
  • Blog Post: Scans vs. Seeks

    Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan. What is the difference between a scan and a seek? A scan returns the entire table or index. A...
Page 1 of 1 (14 items)