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….

1

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 T2…

9

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…

4

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 to run this…

5

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 of a table.  (See the last…

5

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/Os.  As in my…

1

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.  I’ve…

2

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…

2

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 surprise that…

4

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’s begin…

1