Fine tuning indexes 1

[rtgordon]

Here is the query that I am trying to fine tune my indexes for:

SELECT TOP 500 
	T1.alias_ind,
	T2.supplier_ind,
	T2.mfg_id,
	T1.part_id,
	T2.mfg_name, 
	T1.rel_part_num,
	T3.commodity_cd,
	T3.family,
	T3.part_size,
	T4.component_type_desc,
	T3.cap_value,
	T3.voltage, 
	T3.cap_tolerance, 
	T3.temp_coeff, 
	T3.term_style, 
	T3.packaging 
FROM 
	psdb_cap_part_dtl T1,
	psdb_mfg_tbl T2,
	psdb_cap_parameters T3,
	psdb_component_type_desc T4,
	psdb_mainframe_part_dtl T5 
WHERE 
	T1.part_id IN (select part_id FROM psdb_cap_part_dtl where rel_part_num like 'C0805C106%') 
	and T1.part_id = T3.part_id
	and T1.rel_part_num = T5.mainframe_part_num 
	and T1.mfg_id = T2.mfg_id 
	and T2.supplier_ind = 'Y' 
	and T3.component_type_cd = T4.component_type_cd

Now, take T1 for example... I had an index on part_id, rel_part_num, and mfg_id. It was running pretty slow. So, for farts and giggles, I created a clustered index featuring each of those columns. My question is... Is it better to have clustered index with all three only? clustered index with all three plus three individual indexes? three individual indexes without the cluster?

Are there any other performance considerations that I should consider? Any other approaches to fine tuning indexes besides just adding and removing and waiting to see what happens?

Thanks!
gordon

 

[tlbroadbent]

There are many factors when tuning indexes. The best thing to do is experiment with various indexes, use the Index Tuning Wizard to suggest indexes and view query plans using SQL Query Analyzer.

You can find a lot of reading material about the general concepts of index tuning. Start at the following page where I've gathered some good links to SQL Server performance tuning resources on the Internet.

http://tlbroadbent.home.attbi.com/sql_and_access/sql_performance.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rtgordon]

I have been experimenting with the indexes... the weird thing was that when I just had indexes on part_id, rel_part_num, and mfg_id it was pretty slow. I added the clustered index including each of them and it went fast. I removed the individual indexes leaving only the clustered and it went even slower than the first. I was curious about why that happened.

I have been looking in sql bol... thanks for the link. I'll continue messing with it.