February 23, 2007 at 8:39 am
Hi All,
I have the DDL of a table as follows:
Tablename: PersonProfile20070223
Personref varchar no 60 (PK)
ProsNo varchar no 30
SourceNo varchar no 30
InvoiceID int no 4
Branchref varchar no 30
ContactTel varchar no 30
Startdate datetime no 8
location_id int no 4
clientRef int no 4
msa_id int no 4
Address_id int no 4
invoiceNo int no 4
I do get different variations in the sort of queries users perform, it also has a web front end which uses the database.
I am thinking of having the following indexes:
Clustered index on (PersonRef & Startdate)
Non Clustered index on (clientRef & Startdate)
Non Clustered index on (ClientRef, Startdate, InvoiceNo)
Non clustered index on (clientRef, Startdate, ContactTel,msa_id)
The indexes have been based on the popular group by’s that users perform and also the kind of queries that will be executed from the web front end.
I am not very sure whether having multiple indexes on the same column is a good idea, and also what are the rules behind it ?
Thanks
February 23, 2007 at 8:55 am
I'd definitely leave off the second one, the third one should cover anything needed by the second one.
More indexes = more work on any write actions (insert/update/delete), so pick what you need and what you'll see a solid performance gain from. Think about not just the grouping order but also what's being referenced in your SELECT and WHERE clauses.
Not to plug my own work too much, but if you're using SQL Server 2005 you can use my article to help determine what would be / would not be useful:
February 23, 2007 at 8:57 am
once again the answer is "it just depends" you certainly don't need the first non clustered index, and personally I'd probably drop invoicenumber into the third index and do away with the second. As to rules, there are none, and to those who will probably post about indexes slowing inserts ( unlikely ) and updates - I'd be delighted to see proof I can reproduce < grin >
If you consider your application performance , and this is what I've done for four clients over the last couple of years, do I put a 7 column index on my table and reduce io from say, 200k to 5 , that's 1.5GB of page reads which may well devastate your cache and cause table locking thus causing potential blocking on writers, or say well I'll put with that as an index might slow down updates ? No brainer really !! I find the biggest performance degredation for updates and inserts is actually raid 5 and poor disk subsystems.
So , yup multiple wide indexes if it matches your requirements, analyse the plans and see what happens. If Kimberley Tripp or Kalen Delaney are presenting near you get along to their index presentations, well worth the money. My clients want high performance from their systems, if that means lots of indexes, fine.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply