Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2009-11-06 : 07:10:34
|
| I am using SQLS 2005, sp3.I run a large medical database and, today, decided to run dta on a set of procedures, one of which is regularly rather slow. The slow section always centres on a large table (500 million records) that documents patients' test results. I already have a compound clustered index on this table, including a field ([medcode]) that provides a diagnostic code for each test outcome. Today, I was running a procedure that joined a diagnostic lookup to the tests table using the medcode field and, to my surprise, the analyser's report recommended setting a non-clustered index on the medcode field; it also estimated that this would generate a 99% improvement in performance - nice if you can get it!One regular emphasis of technical bulletins - from this and other SQL forums - is that it is bad practice to index a field twice. If I follow the dta recommendation, [medcode] will be part of the clustered index as well as having a non-clustered index of its own.Could anyone advise me about this?Thanks.Chris |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-11-06 : 09:09:30
|
| It will work for the particular query you were running due to the fact that if you aren't using all the other fields in the clustered index on your join, then the optimiser will disregard the index if it feels it can run the query faster without it.Clustered index keys should be kept as small as possible, simply due to this type of situation. You can then add other fields in to non-clustered indexes and include outlying fields. |
 |
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2009-11-09 : 11:47:33
|
| Many thanks for your guidance - this makes good sense, and I will re-appraise my approach to indexes in the light of what you say.Chris |
|
|
|
|
|
|
|
|
database tuning adviser