How Clever Is your Index?

If you Seek an index, would you expect SQL to seek the index once for each query, or once for each operator, or once for each value? You would hope that it was once for each query, but is that true? Well, it depends. I've created a rather contrived example, to demonstrate SQL's behavior.

Firstly, I created and populated a table, and then create an index on it using the following script...

CREATE TABLE LogicalReadsTbl (Col1 INT)
DECLARE @i INT
SET @i = 1

WHILE @i < 100000
BEGIN
     INSERT INTO LogicalReadsTbl
     VALUES(@i)
     SET @i = @i + 1
END

CREATE NONCLUSTERED INDEX IX1 ON LogicalReadsTbl(col1)

...I have created a nonclustered index, but for this particular example, the behavior would be identical with a clustered index (although I will let you prove that for yourself)

Next, I need to make sure that my queries are going to span rows that are stored in multiple pages, so I ran a simple query, and included %%physloc%% (see this post) to see how my rows were organised.

Based on this, I choose to use values between 619 and 626, as the page break was on 623/623.

Next, I ran sys.dm_db_index_physical_stats against my table, to double check the number of levels in the index. As suspected (because of size), there were 2 levels.

So the next thing to do, was turn on IO statistics. (Please bare in mind that because I ran a query including %%physloc%%, I am already expecting all pages of this table to be in the buffer cache, and hence I should see no physical reads, only logical reads) Just for fun, I also turned on Include Actual Execution Plan, then I ran the following query, and checked out the results...

SELECT COL1
FROM LogicalReadsTbl
WHERE Col1 BETWEEN 619 AND 626

Table 'LogicalReadsTbl'. Scan count 1, logical reads 2

...Perfect! SQL performed an index seek, and then, as the pages were next to each other, it read them as one block.

Ok, so now lets look at another query. Now this query will only return 2 rows, not 8, and read exactly the same pages, so we should have an identical query plan, and identical IO stats...right???

SELECT
COL1
FROM LogicalReadsTbl
WHERE Col1 = 619 OR Col1 = 626

Table 'LogicalReadsTbl'. Scan count 2, logical reads 4

...Ok, that's not so good, despite the same execution plan, and the same environmental conditions, and despite the fact that this time, we are reading 1/4 of the number of rows, the IO has doubled. SQL has performed 1 seek, for each page it required.

So be careful, sometime SQL is not as clever as we like to think it might be!!!

Do you find this kind of thing interesting? Please let me know, as it's good to know how to use my blogging time!!!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom