Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

Wednesday, July 29, 2009 1:56 PM

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
Posted by craigfr | 0 Comments
Wednesday, June 24, 2009 9:26 AM

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
Posted by craigfr | 0 Comments
Filed under: ,
Tuesday, April 28, 2009 2:45 PM

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:
Posted by craigfr | 1 Comments
Filed under: ,
Wednesday, March 18, 2009 7:34 AM

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
Posted by craigfr | 0 Comments
Filed under: ,
Wednesday, March 04, 2009 9:27 AM

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
Posted by craigfr | 2 Comments
Filed under: ,
Wednesday, February 25, 2009 9:29 AM

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
Posted by craigfr | 4 Comments
Filed under: ,
Thursday, October 30, 2008 2:40 PM

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
Posted by craigfr | 4 Comments
Filed under:
Tuesday, October 07, 2008 2:44 PM

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
Posted by craigfr | 1 Comments
Filed under: , ,
Tuesday, September 23, 2008 1:45 PM

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
Posted by craigfr | 9 Comments
Filed under: ,
Friday, August 22, 2008 3:42 PM

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
Posted by craigfr | 1 Comments
Filed under:
Tuesday, August 05, 2008 11:33 AM

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
Posted by craigfr | 1 Comments
Tuesday, July 15, 2008 2:03 PM

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
Posted by craigfr | 8 Comments
Friday, June 27, 2008 2:44 PM

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 T1 WHERE (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
Posted by craigfr | 5 Comments
Filed under:
Thursday, June 05, 2008 10:15 AM

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
Posted by craigfr | 1 Comments
Filed under:
Thursday, May 15, 2008 9:39 AM

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,
Posted by craigfr | 0 Comments
Attachment(s): QPTalk.pdf
More Posts Next page »
 
Page view tracker