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: 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 query to fail. In this post, I'll give another example of how query hints can cause problems. As with my last post, this post was inspired by a question submitted by a reader. SQL Server has a documented...
  • Blog Post: Implied Predicates and Query Hints

    In this post, I want to take a look at how two seemingly unrelated features of SQL Server can interact to cause a problem. The idea for this post came from a question submitted by a reader. Let's begin. Consider the following trivial schema and query: CREATE TABLE T1 (A INT, B INT) CREATE TABLE...
  • Blog Post: 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 join and showed how this sort can significantly improve performance . In an earlier post , I discussed how SQL Server can use random prefetching to improve the performance of a nested loops join. In this...
  • Blog Post: 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 I/Os. In this post, I'll demonstrate directly how such a sort can impact performance. For the following experiments, I'll use the same 3 GByte database that I created last week . The system I'm using...
  • Blog Post: 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 (particularly for conventional rotating hard drives). For this reason, SQL Server often favors query plans that perform sequential scans of an entire table over plans that perform random lookups of only a portion...
  • 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: 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 pushes the conversion below a join. The result is that the conversion may be run on rows that do not join which could lead to avoidable failures. I ran this query on SQL Server 2005. After I published that...
  • Blog Post: 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, '0') INSERT T1 VALUES (1, '1') INSERT T1 VALUES (99, 'Error') SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1 There is no way to convert the string "Error" into an integer, so it should come as no...
  • Blog Post: Query Plans and Read Committed Isolation Level

    Last week I looked at how concurrent updates may cause a scan running at read committed isolation level to return the same row multiple times or to miss a row entirely. This week I'm going to take a look at how concurrent updates may affect slightly more complex query plans. Nested Loops Join Let...
  • Blog Post: Semi-join Transformation

    In several of my prior posts, I’ve given examples of semi-joins. Recall that semi-joins essentially return a row from one input if we can find at least one matching row from the other input. Here is a simple example: create table T1 ( a int , b int ) create table T2 ( a int , b int ) set...
  • Blog Post: Parallel Hash Join

    SQL Server uses one of two different strategies to parallelize a hash join . The more common strategy uses hash partitioning. In some cases, we use broadcast partitioning; this strategy is often called a “broadcast hash join.” Hash Partitioning The more common strategy for parallelizing a hash...
  • Blog Post: Parallel Nested Loops Join

    SQL Server parallelizes a nested loops join by distributing the outer rows (i.e., the rows from the first input) randomly among the nested loops threads. For example, if we have two threads running a nested loops join, we send about half of the rows to each thread. Each thread then runs the inner side...
  • Blog Post: Subqueries: ANDs and ORs

    In my “Introduction to Joins” post , I gave an example of how we can use a semi-join to evaluate an EXISTS subquery. Just to recap, here is another example: create table T1 ( a int , b int ) create table T2 ( a int ) create table T3 ( a int ) select * from T1 where exists ( select ...
  • Blog Post: Subqueries in CASE Expressions

    In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just evaluate the CASE expression as we would any other...
  • Blog Post: Summary of Join Properties

    The following table summarizes the characteristics of the three physical join operators which I described in my three prior posts. Nested Loops Join Merge Join Hash Join Best for … Relatively small inputs with an index on the inner table on the join...
  • Blog Post: Hash Join

    When it comes to physical join operators, hash join does the heavy lifting. While nested loops join works well with relatively small data sets and merge join helps with moderately sized data sets, hash join excels at performing the largest joins. Hash joins parallelize and scale better than any other...
  • Blog Post: Merge Join

    In this post, I’ll describe the second physical join operator: merge join (MJ). Unlike the nested loops join which supports any join predicate, the merge join requires at least one equijoin predicate. Moreover, the inputs to the merge join must be sorted on the join keys. For example, if we have a join...
  • Blog Post: Nested Loops Join

    SQL Server supports three physical join operators: nested loops join, merge join, and hash join. In this post, I’ll describe nested loops join (or NL join for short). The basic algorithm In its simplest form, a nested loops join compares each row from one table (known as the outer table)...
  • Blog Post: Introduction to Joins

    Joins are one of the most important operations performed by a relational database system. An RDBMS uses joins to match rows from one table with rows from another table. For example, we can use joins to match sales with customers or books with authors. Without joins, we might have a list of sales and...
Page 1 of 1 (20 items)