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 |
|
zntd
Starting Member
2 Posts |
 Posted - 2007-02-08 : 10:03:03
|
| The dbsize is 6 gb. Larger tables have approx 15 million rows. Running SQLServer 2000.I have identified many missing indexes that RIGHTFULLY need to be added.The indexes are not needed for performance reasons. Only because academically the indexes should exist. The server (hardware) is very fast, and the users are very happy with the performance (without adding the missing indexes). Users would NOT PERCEIVE the difference between 0.005 seconds (with NO indexes) versus 0.0005 seconds (with indexes). I just made-up these performance figures to illustrate my point. The server is very fast. So, should a person place indexes where indexes RIGHTFULLY should be placed, although the users will not PERCEIVE the benefit ? However, there is a REAL (not perceived) disadvantage that the indexes will consume much diskspace. I was explaining to our programmer that I would be adding some missing indexes. I was explaining the advantages versus disadvantages. As I was doing this, it was appearing that there were more disadvantages than advantages to adding the missing indexes. Here was my argument -Disadvantages--------------1) Our in-house VB application (mission critical) MIGHT experience a bug or two. I have seen this happen in the past once or twice. When I added an index, the app would have errors similar in style to MDAC type of errors. It was resolved by removing the offending new index.2) The database will grow by 25 - 40 percent ( many indexes are missing from large tables).3) User will not PERCEIVE a difference in speed because the app responses instantaneously already.Advantages----------------1) It is the proper thing to do (academically speaking).2) As the server (hardware) ages and as data grows, the indexes will be in place to assist.So, you see my problem. The argument seems strongest for NOT placing indexes. So, I began to have self doubts in my indexing project. What would you do and why ? THANKS |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-08 : 11:07:12
|
| I agree with the space increase.I agree that it would have been better that they were there all along, because then they would have been "within" the QA that has already been done. Although I would be surprised if there would be any bugs - but you might get things like INSERT taking longer because of the need to also insert into the index(es).I'm amazed that there will be no tangible speed improvement. Assuming you currently have something like this:SELECT ...FROM MyTableWHERE MyPK = 'xxx'that will be instant. ButSELECT ...FROM MyTableWHERE MyNonIndexedField = 'yyy'I would expect to take a significant amount of time on 15 million rows, no matter how good your hardware is!Personally we don't fret too much about indexes until we have a performance problem. Tables are designed with the indexes we think are likely to be beneficial, and during the life of the application that opinion may change! and thus we add/drop indexes as appropriate.But assuming you have a Primary Key on each table, and NO secondary indexes AND a reasonable amount of queries that do NOT use the Primary key as their criteria then I'm amazed that you can do without the indexes.But if its just "Display record XXXX on an edit screen" then yes I doubt you need anything indexed except the Primary Key.One possible additional downside is that you will need a maintenance routine to "defrag" the indexes periodically, and to Update their Statistics (but you already need this task on your Primary key, so it may be no big deal to add more)One additional reason to tackle this now is if the Server might get extra databases on the future - that could bring to light the inefficiency of the current design in short order! Also, as you say, if your data grows (e.g. to a point where it can less easily cache as large a percentage as it currently is doing) then performance will most likely drop then too.Kristen |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-08 : 11:36:25
|
| if the app is fast enough then don't add the index. If the app is slow and the index is shown to improve the speed, then add the index. It is as simple as that.-ec |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-09 : 15:57:51
|
| Be careful of placing even value on all items. If the users are not complaining I would tend to leave the indexes out myself. However, if you are seeing a trend toward higher disk IO, or higher CPU over time, it may be time to put in one or two of the higher value indexes. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2007-02-09 : 17:00:17
|
| I'd like to see the execution plan, and have basic understanding of the data usage patterns.Before giving any opinions on whether the index is good or not.If selecting from a 15m row table with/without an index makes such a small impact, I suspect the index is not used at all,or is on another table.rockmoose |
|
|
|
|
|
|
|
|
Usefulness of index on fast cpu