SQL Server Performance Forum – Threads Archive

Indexing Question

There are 2 columns that are always in the WHERE statement: ClientID (INT) and Deleted (BIT) What’s the tradeoff of indexing each column independently or CREATE INDEX IDX_UsersClientIDDeleted ON dbo.Users (ClientID, Deleted) Any reason the latter is better from a performance point of view? Sam

Welcome Sam, Should CliendID be a primarky Key, then u have automatically an index created (first appreciation)
Secondly, it is different to keep 2 indexes than one, and in your case (you said the WHERE is "always" like that) I would only create one (with the 2 fields) Performance? indexes are always a big mystery (one of those "Mysteries of the DBA" large chapter) and the task of the DBA is to try to gather and analyze de different info around that table and its usage: size, number of reads, number of writes (does the BIT change a lot of times during the day? will the system have to rebuild the index many times?), etc etc … In your particular case, anyway, if the ClientID is a primary key -I insist- the system will get to the record using the key, directly, and then decide if "DELETED" is on/off … so, no extra index needed here …

ClientID is not a primary key. Neither column changes frequently. Is there a general rule on whether two indexes might perform better (and in what way) than a single clustered index?

Hey Sam….hitting all the boards now? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Derrick, as we say in spanish you are a "lost case" ("Un caso perdido", the free spanish lesson will cost u 1 euro) Watch out Sam! don’t get confused with CLUSTERED indexes and NON CLUSTERED indexes, which in fact is a characteristic of indexes that should be taken under consideration (reminder: Clustered indexes force data to be contiguous on disk, so, there’s ONLY ONE potential CLUSTERED index in every single table) … So, a clustered index is recomendable for "very static" tables, or, at least, those without a high writing workload … Then, there is nothing to do in your case, because we were talking about 2 indexes instead of one with two fields, right? This is another concept. Therefore, no general rule for mixed concepts …. anyway, I’d like the masters come up with the calculator and show us the formula, if any … Derrick, wake up!

I wish I would stop saying "clustered" when I mean "compound". I can’t find a decent paper on designing indexes for query optimization. Hi Derrick ! Seems like index questions run outta gas at SQLTeam, I thought I see what’s up here.

So far, u are not going to find any "decent" paper around … I guess thats one of those "well-kept" secrets of performing DBA tasks … hey, thats why we are getting paid!

Sam,
I know you’re a seasoned SQL Server user, so I’m not sure whether this will be new to you
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,7/id,77/
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,7/id,74/
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,8/id,57/
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,8/id,58/
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,8/id,62/
http://www.insidesql.de/component/option,com_bookmarks/Itemid,42/task,view/catid,8/id,61/ In addition to that, do a forum search here, IIRC this had been discussed here before. wabbash, some minor corrections: Only at creation or recreation time the data is physically stored in a sorted manner when you have a clustered index (CI) on a table. SQL Server does _not_ guarantee that order when data is added, deleted or whatsoever. That would be far too cost intensive. Therefore SQL Server maintains the page chain, a doubly linked list which is ordered according to the CI keys. From what I’ve seen so far, a clustered index is recommended for almost every table. You can leave out history, protocol… tables where hardly never looks at that data again. –Frank
http://www.insidesql.de

]]>