Usefulness of index on fast cpu

Question

Usefulness of index on fast cpu

zntd

Valued Member

Points: 70
More actions
February 7, 2007 at 2:01 pm

#116092

We all know that it is "best practice" to place indexes where justified (based on usage). However, if the server is very fast, would you still place an index based on usage as opposed to need. The index is not needed because the server is new and fast, and the application responds instantly for the users. So, there would be no PERCEIVED improvement with indexes. However, placing indexes would increase the disk space used.
What would you do and why ? THANKS

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Indexes will reduce query I/O cost...if the table is small or no where clause in you the query at that time... you don't see any diffence...

Enable STATISTICS I/O run the query and see the difference with and without indexes...

http://www.sql-server-performance.com/gv_clustered_indexes.asp

http://www.sqlmag.com/Article/ArticleID/21038/sql_server_21038.html

MohammedU
Microsoft SQL Server MVP

zntd Valued Member Points: 70 More actions · Answer 2

Thanks for the reply. But, I am not a beginner. I am an experienced DBA, and this is an "advanced" question. Not "Indexes 101" type of question.

The dbsize is 6 gb. The tables have approx 15 million rows. Running SQLServer 2000.

So, techinally, the performance is much quicker with the indexes. But, users can NOT PERCIEVE 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 PERCIEVE the benefit ? However, there is a REAL (not percieved) disadvantage that the indexes will consume much diskspace.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 3

I'm of the opinion that you should review your indexes regularly to ensure they are helpful and useful and add/remove indexes where necessary. That being said... data grows.

Placing an index and managing it early on ensures that as the data grows you are already prepared to meet the performance needs of your users. However you're right, if you have a table which is accessed often but only contains a hundred or a thousand records... it may not be worthwhile. Indexing is not an exact science. I would think more about what your growth rate will be in tables and index accordingly.

zntd Valued Member Points: 70 More actions · Answer 4

Thanks Aaron !!! This is the type of discussion that I am seeking !!!

Here is the situation. As a professional DBA, I would feel embarrassed if someone told my boss that I was a bad DBA (not doing my duties) if I did not place indexes where they should be. The indexes are not needed for performance reasons. Only because academically the indexes should exist. The server is very fast, and the users are very happy with the performance (without adding missing indexes).

I am just feeling uncomfortable to tell my boss - "No indexes are needed because the users will not see any performance improvement".

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 5

Well, therein lies the difficulty.

I tend to err on the side of the more "academic" approach, personally. Though I do try to take into account whether the tables will have high insert/update/delete activity. Whether or not you add indexes now, you should still make it clear to your supervisors that you intend to monitor performance as the database grows and analyze appropriately. I usually try to do it monthly for the first few months after a product launches and then every 3 or 6 months afterward.

You might check out this article (shameless self-plug):

http://www.sqlservercentral.com/columnists/aingold/2770.asp

MohammedU SSCoach Points: 19644 More actions · Answer 6

I agree with Aaron, if you rely on the HW it is upto you...but it is not advisable...

It matter between 0.5 and 0.05 execution times when the procedure executes many times...

MohammedU
Microsoft SQL Server MVP

zntd Valued Member Points: 70 More actions · Answer 7

Thanks Aaron and Mohammed !!!

Your comments were very helpful.

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 app) MIGHT experience a bug or two. I have seen this happen in the past. When I add an index, the app would have errors similar in style to MDAC type of errors.

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. Thanks to your comments, I will move forward (knowing that I am doing the Right thing) with placing indexes where justified.