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 |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2010-02-26 : 11:16:22
|
| Hi I'm pretty new to indexing and have a few questions:1) I have a table with 4.5 million transactions - the primary key (SRN) currently has a non-clustered index on it. I use this primary key quite often for joining, would it speed up my queries if I used a Clustered index on this rather than non-clustered? Should I remove the current non-clustered index first, or just add a culstered index to SRN so it has both a clustered and non-clustered?2) If I create an index:CREATE NONCLUSTERED INDEX IND_MULTIPLE ON TABLE(FIELD1, FIELD3, FIELD5)What benefits this does have when querying the data, i.e. would the query only speed up when I query all those fields at once? or does it have the same affect as indexing the fields one at a time? i.e.CREATE NONCLUSTERED INDEX IND_1 ON TABLE(FIELD1) CREATE NONCLUSTERED INDEX IND_2 ON TABLE(FIELD3) CREATE NONCLUSTERED INDEX IND_3 ON TABLE(FIELD5) Many thanks! |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-26 : 11:53:29
|
| I was told that if you want to sound like an expert, you should answer these questions with the phrase, "It depends..."It depends on:1) How often your current clustered index gets used to filter data2) What type of filtering. Ranges, such as data ranges, like to use clustered indexes.3) Amount of data returned - A single row returned has different needs from large data sets4) A plethora of other characteristicsWhat you can do is "guess and test". If you have a typical work load and a test server, move your database to the test server, make the suggested indexing changes and measure their impact on performance.As to the question of whether to keep your non-clustered index in addition to the clustered index, the answer is... (wait for it)... "It depends"! If your non-clustered index is a covering index, it could help performance; if not, it probably won't get used. You can "guess and test" this by looking at the execution plan generated. You can also use the sys.dm_db_index_usage_stats view to see which indexes are not being used.HTH=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-26 : 13:59:18
|
" 2) If I create an index:CREATE NONCLUSTERED INDEX IND_MULTIPLE ON TABLE(FIELD1, FIELD3, FIELD5)What benefits this does have when querying the data"1) FIELD1 should be the most selective field. If FIELD1 is (say) a bit field with values of either 1 or 0 in roughly equal proportions it will never be used. If it has values that are virtually unique within the index then the index will be used whenever a query can benefit from it.SQL only considers the first field in the index when making its "Selectivity" decision.2) FIELD2 & FIELD3 may either be part of your normal JOIN (i.e. you have a multi-part key), or they may "cover" the query.Consider:SELECT ...FROM TableA AS A JOIN TableB AS B ON B.ID = A.IDWHERE B.IsActive = 1 An index on TableB.ID will be used to assist thing query, a compound index on TableB for keys ID, IsActive (in that order - most selective first) will not only assist the join but also "cover" the WHERE clause.Separate indexes on FIELD1 and FIELD2 will NOT be used to "Cover" the query in this way.3) An index can cover the "Select" too (rather than just the JOIN / WHERE clause). This can sometime be turned to benefit. |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-26 : 14:38:30
|
| How to index is driven more by usage patterns than by data definition. For example, if you often retreive only one record from this table, there will be almost no difference between a clustered and a non-clustered index. However, if you often retreive a large number of records with contiguous SRN values, then a clustered index will be very beneficial. Another consideration is that you can only have one clustered index. So, even though you may gain performance on one query by putting a clustered index on SRN, you may lose performance overall because you no longer have a clustered index on whatever column it used to be on.I find that one of the hardest things to keep in mind is to never think about clustered indexes on a query-by-query basis. A well-planned clustered index can make almost any query faster. However, the clustered index should be used where it helps the most. It's kind of like deciding how to spend money. Almost every purchase looks like a good idea if you only consider the value of what you are buying. However, an intelligent purchase is made by deciding if this money could be spent better elsewhere. |
|
|
|
|
|
|
|
|
Indexing Query