July 2011 - Posts

• Enforcing Uniqueness for Performance

  A little while back, I posted a short series on seeks and scans, and one of the things I highlighted was the difference between a singleton seek and a range scan.  You can find that post here , if you want a refresher. Anyway, the broad point is Read More... Posted Friday, July 29, 2011 5:38 AM by Paul White | 7 Comments

• Join Performance, Implicit Conversions, and Residuals

  You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache Read More... Posted Tuesday, July 19, 2011 1:17 AM by Paul White | 15 Comments Filed under: Internals, Query Plans, Undocumented, Performance, Joins, Conversions

• Bitmap Magic (or… how SQL Server uses bitmap filters)

  Question: Can a parallel query use less CPU than the same serial query, while executing faster? The answer is yes; and to demonstrate, I'll use the following two (heap) tables, each containing a single column typed as INTEGER: Let’s load the first table Read More... Posted Thursday, July 07, 2011 5:44 AM by Paul White | 10 Comments

• Undocumented Query Plans: The ANY Aggregate

  As usual, here’s a sample table: With some sample data: And an index that will be useful shortly: There’s a complete script to create the table and add the data at the end of this post.  There’s nothing special about the table or the data (except Read More... Posted Saturday, July 02, 2011 4:51 AM by Paul White | 9 Comments