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…

6

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

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…

11

Dynamic Partition Elimination Performance

In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient implementation of dynamic partition elimination as compared to SQL Server 2005.  In response, a reader posted this comment asking how much dynamic partition elimination really costs in SQL Server 2005.  While I was sure that the answer is…

3

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 partition id column. …

2

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 the example…

13

Subqueries in BETWEEN and CASE Statements

Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT)CREATE TABLE T2 (A INT, B INT) SELECT *FROM T1WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs to be evaluated once for each row of T1.  Indeed running on…

8

Implicit Conversions

In my last couple of posts, I wrote about how explicit conversions can lead to errors.  In this post, I’m going to take a look at some issues involving implicit conversions.  SQL Server adds implicit conversions whenever you mix columns, variables, and/or parameters with different (but compatible) data types in a single expression.  For example,…

5

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