| Author |
Topic |
|
REEPER
Yak Posting Veteran
53 Posts |
 Posted - 2006-09-21 : 16:25:29
|
| How do having multiple table indexes affect system performance? Even if those indexes are not used?MCP, MCSD |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-21 : 16:34:04
|
| It impacts DML operations.Tara Kizer |
 |
|
|
REEPER
Yak Posting Veteran
53 Posts |
Posted - 2006-09-21 : 16:36:16
|
| What does that mean and how does it affect performance?MCP, MCSD |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-21 : 16:37:59
|
| DML is data manipulation language. It impacts performance as the index has to be updated if it is impacted by the DML operation.Tara Kizer |
|
|
|
REEPER
Yak Posting Veteran
53 Posts |
Posted - 2006-09-22 : 08:51:58
|
| So if I have secondary indexes that are not being used on about 10 large tables what kind of performance gain could be seen if I erase them?MCP, MCSD |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-09-22 : 09:16:36
|
| Presumably your MCP, MCSD is not in SQL Server database administration, then?The performance benefits or otherwise that you may or may not realise from removing or adding indexes will be entirely dependant on your application and what magnitude and sort of processing happens on your database.It would be entirely pointless for us to sit here with no knowledge of your system and give you some kind of numeric value of performance increase you'd see. You'd have to test it yourself to get real data.-------Moo. :) |
|
|
|
REEPER
Yak Posting Veteran
53 Posts |
Posted - 2006-09-22 : 13:36:02
|
| You are correct the .NET MCSD is mostly C# based with a sprinkle of Anaysis and requirments mixed in. I'm just asking a general question and am having a hard time getting a general answer. I understand adding indexes can increase perfomrance significantly if used. I'm just asking the question - What if they are not used? Not asking the question from an application standpoint just from the database server level - nothing more.MCP, MCSD |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-22 : 13:42:43
|
| If the indexes are not being used, then they should be dropped so that they do not negatively impact performance on DML operations. We can't measure what kind of impact it has when they exist and when they do not exist. That measurement can be taken during testing.Tara Kizer |
|
|
|
REEPER
Yak Posting Veteran
53 Posts |
Posted - 2006-09-22 : 13:48:31
|
| Just out of curiosity is a relative to anything like table size, or specific kind of sql call?MCP, MCSD |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-22 : 13:53:56
|
| The performance impact will be related to how much data needs to be updated in the index. It also will be related to the width of the index.Tara Kizer |
|
|
|
REEPER
Yak Posting Veteran
53 Posts |
Posted - 2006-09-22 : 15:14:06
|
| Thats exactly the answer I was looking for Tara - I just wasn't smart enough to ask the right question.thx!MCP, MCSD |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-09-23 : 16:32:15
|
| Although, they can decrease SELECT operations sometimes. IF you have redundant indexes, especially if you have the same columns covered by a composite and single-column index. Theoretically, the optimizer should just always magically pick the best index. Alas, theory is just that.....theory.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-09-25 : 05:29:08
|
| Yep. That particularly can happen if there's a large number of indexes. The optimizer can stop looking for a good index to use once it gets bored of trying a few and use an non-optimal one instead.-------Moo. :) |
|
|
|
|
Indexes