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 […]