SQLskills SQL101: Query plans based on what’s in memory

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]

Reconciling set-based operations with row-by-row iterative processing

Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators. The crux of the discussion is: if SQL […]

DBCC CHECKDB performance and computed-column indexes

[Edit 2016: The team ‘fixed’ the problem in SQL Server 2016 by skipping consistency checking these indexes unless WITH EXTENDED_LOGICAL_CHECKS is used.] It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC […]

Two great blog series on joins and query plan operators

I was doing some research this morning for some query tuning on a client system and I came across a couple of blog series with some excellent posts for those looking to learn more about query plans (and as a handy reference). The first series is by Craig Freedman on the Query Processor team at […]

Which index will SQL Server use to count all rows

This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable? The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I/O cost. Let me quickly […]