More on Implicit Conversions

Yesterday, a reader posted a question asking me to comment on SQL Server’s algorithm for choosing implicit conversions.  When SQL Server encounters an expression with mismatched types, it has two options.  It can execute the query with an implicit conversion or it can fail with an error.  Before digging into the implicit conversion scenario, let’s…


Correction to my prior post on sys.dm_db_index_operational_stats

In this post about the sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats DMVs, I wrote: Another less important difference between these DMVs is that sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted while sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used. The Books Online page…


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


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…


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…


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…


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…


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 to different indexes.  These DMVs are documented in Books Online…


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…


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/O is so important, consider the CPU to I/O performance gap.  The…