Nonclustered indexes require the “lookup” key in the b-tree when?

I received a great question in email and it’s something I cover in our IEPTO1 (Immersion Event on Performance Tuning, Part 1) so I thought I’d write a post about it… Question:  When you have a non-unique key value in a nonclustered index, SQL Server adds the RID / Row Identifier (if the NC is on a […]

Removing duplicate indexes

SQL Server has a horrible habit of letting bad habits move forward (for the sake of backward compatibility [e.g. autoshrink]). And, I do understand *some* of the reasons for allowing some of these “features/options” to move forward. However, there are a few that frustrate me and I’ve talked about this one quite often. SQL Server […]

How can you tell if an index is REALLY a duplicate?

There’s a lot of confusion around duplicate indexes and I think a lot of it has to do with how sp_helpindex (or even SSMS) display what’s in an index. What’s actually in the index – and how it’s structured – are not always what they seem. This was the original motivation behind my rewrite of […]

Trusting the tools!

Well, it's been a crazy few weeks! Paul and I have been booked solid with client engagements, Immersion Events, conferences and other things (like chapter editing for the upcoming SQL Server Deep Dives Volume 2) and so I've been a bit behind with blogging. I have a ToDo list of items to blog about but […]

Indexes: just because you can, doesn’t mean you should!

I've decided to create a new series of posts – just because you can, doesn't mean you should! These are going to go in a new category called "Just because" and I've already got a few lined up in this series. The first one is one I've spoken about often and one that's near/dear to […]

OLD: sp_helpindex rewrites

I realize that I’ve created quite a few posts (and rewrites) around sp_helpindex and recently I’ve been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it’s not so obvious where to get it and/or what to do to install it. To make it easier… I’m just going to do a […]

More considerations for the clustering key – the clustered index debate continues!

OK, I've talked about the clustering key many times. Here, I thought I'd bring together one final series of details (and links) to help you really understand why I'm so adamant about DESIGNING a clustering key and not just letting SQL Server pick it for you (for example when it defaults to making the primary […]

(OLD): A new and improved sp_helpindex (jokingly sp_helpindex8)

NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! OK, I first blogged about re-writing sp_helpindex here as sp_helpindex2 (April 2008). Shortly thereafter (Aug 2008), a reader found a bug and when I went digging, so did I (a couple of others). I did […]

The Tipping Point Query Answers

OK, I'll definitely take a beating from all of you for having gone so long between my survey posts and now. I won't even go into the details but between some crazy work schedules, multiple sinus problems and even migraines… well, I've been a bit behind. Let's just say that April/May were rough at best. […]

Tipping Point Queries – More Questions To Really Test You!

OK, so this is interesting. I've got a few answers to my last survey (Tipping Point Query #1) and well, there's a good mix of answers (and, yes, some are correct! ;)). Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. […]

Why aren’t those nonclustered indexes being used?

Along the same lines of improving database design and getting better performance on SQL Server (which [IMO] DOES take an experienced SQL Server database developer – but, we'll talk more about "whose job this really is" in many more posts and probably even a RunAs – which Richard and I just setup to record on […]