Grumpy Old DBA

I’ve noticed that of late I’ve become a bit more critical of a well known publication that I suspect many DBA’s read. I have subscriptions to a number of publications and for the ones that I pay for I’m generally quite content.

I like paper / hard copy because I can make use of time where it’s just not practical to have a laptop or PC – and I spend my working day in front of a screen so sometimes it’s nice to escape to the garden or the pub or on the train, say travelling to a SSUG meeting without a PC.

So what’s set GrumpyOldDba off now you may ask? Well there’s an article been published about removing duplicate indexes – the Microsoft guys blogged this, with code, ages ago and I spent some time looking at this type of analysis as part of my index analysis series.

However it’s not quite so easy as it sounds and leaving aside duplicated single column non clustered indexes, which are obviously bad, the actual matter of where indexes have the same columns but in different orders or contained within is quite a complex matter and worth a whole lot of time on its own.

( Here’s the original blog from June 2007  http://blogs.msdn.com/mssqlisv/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx)

Taking the above indexes to be all standard non clustered indexes we can say that there’s a fair bit of duplication and in fact index 3 is already contained within index 1

Sadly with anything like this it just depends, and generally I’d say that index 3 is probably unwanted – however it may cover a query exactly, in which case index 1 might be ignored, and in certain joins one index may actually be preferred over another.

So what I’m saying is that other than basic duplicates it’s actually quite tricky, obviously you can check the dmv’s ( sql 2005 onwards ) to actually see if the index is being used, if you’re using 2000 or 7.0 then you can’t.

So what really got me worked up was the statement that creating a secondary index over a clustered index was a bad idea, now to me that such an article appears in this magazine is very disappointing and I’m also disappointed with the author – to me it shows a lack of editorial knowledge and like the review of the backup utilities last month isn’t what I’d expect from such a respected magazine. I’m also frankly becoming less and less impressed by the fact that many articles are only partly published and you have to go to the website for the full article. Maybe it’s a sign of the times that we don’t actually want paper copy any more.

Anyway here’s a very simple example of secondary indexes over the top of clustered indexes. The table I used has 2 million rows and is just under 16gb in size ( Let’s do real world examples here <grin> ) It’s a variation of the table in used in  “Analysing Index Part 4 – Size does matter”

http://www.grumpyolddba.co.uk/sql2005/working%20with%20indexes%204.mht

This is the test table and the code used to populate it. You might want to find other ways of populating the table if you’re not running on a server as I figure this will run for a few hours on a laptop! There were distinct reasons why I wanted to generate data this way for some previous tests, the only important factor for this tests is that each row should exceed half a page so as to force one row per page.

Ø  Here’s the output from sp_spaceused

Ø  and from sys.dm_db_index_physical_stats

Ø  Here’s my table after adding a clustered primary key on the integer identity column, NumKey

Ø  Extracting index information from sys.dm_db_index_physical_stats

And the results

Select 50 rows from the table by PK

select * from dbo.TestTable where NumKey between 50 and 100

(51 row(s) affected)

Table 'TestTable'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is fair enough because at 4,577 bytes per row it requires 51 page requests to return the data rows

Ø  If we just select the integer PK column it still requires the same io to return the result set

select numkey from dbo.TestTable where NumKey between 50 and 100

(51 row(s) affected)

Table 'TestTable'. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ø  Just out of pure interest and as part of another test I was running I dropped the 51 values into an IN statement

select numkey from dbo.testtable where numkey

in (50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,

71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,

92,93,94,95,96,97,98,99,100)

(51 row(s) affected)

Table 'TestTable'. Scan count 51, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ø  The same for two values?

select numkey from dbo.testtable where numkey in (50,51)

(2 row(s) affected)

Table 'TestTable'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So that’s making use of the clustered PK on the integer column, returning just the integer value.

Now create a non clustered secondary index over the PK

create index idx_TestTable_Numkey on dbo.TestTable(Numkey);

go