9

We have a mid-size SQL Server based application that has no indexes defined. Not even on the the identity columns. I suggested to our moderately expensive application consultant that perhaps we might get better performance (particularly as our database grows) by creating some indexes on appropriate fields, and he said:

"Indexes will significantly impact other areas of the application and customers should not create them under any circumstances."

Anybody ever heard of anything like this? Are there ever circumstances where one should not create any indexes? I can see nothing special about this app - it's got int identity columns, then lots of string columns, bunch of relational tables but nothing special or weird that I can see.

Thanks!

[EDIT: the identity columns are not using "identity specification", they seem to be set by the program, looking at the database with Management Studio, I can find NO indexes...]

FOLLOWUP: At a conference I asked the CEO (and chief architect) of the company producing this product about this, his response was that they felt for small to midsize deployments, the overhead associated with maintaining indexes would have more of a negative to overall user experience (the application does a lot of writes) than the benefits of the indexes would offset, but for large databases, they do create indexes. The tech support guy was just overzealous and very unhelpful with his answer. Mystery solved.

Aerik
  • 2,307
  • 1
  • 27
  • 39

8 Answers8

4

There is such a thing as over-indexing, especially in INSERT and UPDATE heavy applications with very large tables. So the answer to the question in your title is yes, it can sometimes be a bad idea to add indexes.

That's quite a different question from the one you ask in the body of your question, which is "Is it ever normal to have NO indexes in a SQL Server database". The answer is that unless you're using the database as a "write-only" system, in which data is added but only read after being bulk extracted and transformed into a another data store, it's exceedingly unusual not to have some indexes in the database.

Your consultant's statement is odd enough to make me believe that you may have left some important information out of your description. If not, I'd say he's nuts.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • I actually suspect he is covering up for such a glaring oversight - that his company would rather give us bad advice than have us know they missed something like database indexes in their design. – Aerik May 21 '10 at 01:59
  • Either that, or he is a total idiot. Been in many projects, seen that too - including some total bunkhead database specialist making all fields TEXT fields becuase the length was not part of the object model (ergo: non indexable -even things like product number). People like that AREA around, and sometimes are so as consultants. Sadly enough. – TomTom May 21 '10 at 06:55
  • If I had to do without lengths I'd use postgresql in which varchar(2000000000) is valid and indexable and doesn't cost any more than varchar(100) if it turns out varchar(100) was all you needed. – Joshua Jun 15 '10 at 19:53
4

Hire me and I'll create the indexes for you. 14 years' Sybase/SQL Server experience tells me to create those !darn! indexes. Unless your table has less than 500 records each.

My idea is that an index hash node is roughly sized to 1000.

The other thing you need to look out for is whether your consultant has normalized the tables. Perhaps, the table has 500 fields/columns, containing more than one conceptual entity or a whole dozen of conceptual entities. And that could be why he is nervous about creating indexes, because if there are 12 conceptual entities in the table there would be at least 12 set of indexes - in which case, he is absolutely true - under no circumstances ... blah blah.

However, if he indeed does have 500 columns or detectably multiple conceptual entities per table - he is a very very lousy data design engineer. In all my years working with more experienced data engineers, our tables rarely exceed 20 columns. 5 on the low side, 10 on the average. Sometimes for performance' sake we do allow mixing two entities in a table, or horizontalizing row occurrences into columns of a table.

When you look at the table design you can with an untrained eye see Product, Project, BuildSheet, FloorPlan, Equipment, etc records all rolled into one long row. You cannot mix all these entities together into one table.

That is the only reason I know why he could advise you against having indexes. If he is doing that, you should know that he is fraudulently representing his data design skills to your company and you should immediately drop him from your weekly contractual expenses.

OK, after reading larry's post - I agree with him too.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Blessed Geek
  • 21,058
  • 23
  • 106
  • 176
  • There are some tables with a lot of columns, but they don't seem to be containing multiple conceptual entities. The larger tables (column-wise) have lots of attribute-data that seems to be in a reasonable group in that table. – Aerik May 22 '10 at 14:59
  • I've seen what I thought was a good table with 30 columns. But yeah tables follow a Poisson distribution focused on about 5. – Joshua May 23 '10 at 16:23
3

Do you have the disk space to spare? I've seen cases where the indexes weighed more than the table.

However, No indexes exist whatsoever! There can't be a case for that except for when all read operations need the entire table.

Joshua
  • 40,822
  • 8
  • 72
  • 132
  • We have plenty of disk space. And our case is pretty typical: big table, and a read operation is generally seeking for one specific row, or doing a SELECT TOP ... ORDER BY query. So it's not reading the whole table. – Aerik May 21 '10 at 01:37
  • Actually it is - without index. Without any index it only CAN read the whole table for anything. – TomTom May 21 '10 at 06:52
  • 1
    SELECT TOP ... ORDER BY benefits greatly from an index on the ORDER BY column. – Joshua May 21 '10 at 15:03
  • TomTom - you're right, it's haveing to do a table scan for every query... I meant he's not purposefully retrieving the whole table – Aerik May 22 '10 at 14:51
  • If they have no disk space to spare for indexes, they shouldn't be having a database. Paper is always an option. – Alex M Jun 19 '23 at 15:14
  • @AlexM: Depends. Index-less DB is still quite fast for many things if the alternative is indexed paper. – Joshua Jun 19 '23 at 16:03
2

Columns with key constraints will have an implicit index on them anyway. So if you're always selecting by the primary key, then there's no point adding more indexes. If you're selecting by other criteria, then it makes sense to add indexes on those columns that you're querying on.

It also depends on how insert-heavy your data is. If you're inserting more often than you're querying, then the overhead of keeping the indexes up to date can make your inserts slower.

But to say you "should not create [indexes] under any circumstances" is a bit much.

What I would recommend is that you run the SQL Server Profiler tool with some your queries. This tool will recommend which indexes to add that will have the biggest effect on performance.

Dean Harding
  • 71,468
  • 13
  • 145
  • 180
  • The application is definitely skewed much towards reads than writes - it seems to do an awful lot of individual SELECTs rather than utilizing joins – Aerik May 21 '10 at 01:32
  • I've added a little about the SQL Server Profiler tool. Much cheaper than expensive "consultants" who talk outta their ass, and actually quite effective, too ;) – Dean Harding May 21 '10 at 01:42
1

In most run-of-the-mill applications, the impact of indexes on insertion performance is a bit of non-issue. You're usually better off creating the index and if insertion performance drops dramatically (which it probably won't) you can try something else. Obviously there are some exceptions, where you should be more careful, like tables that are used for logging for instance.

As mentioned, disk space can be an issue.

Creating irrelevant indexes (e.g. duplicates) will also waste microseconds and occasionally result in a bad query execution plan.

The other problem I've seen is with strangely code third-party applications that generate parts of the database at runtime, and can delete or choke on indexes that they don't know about.

In the vast majority of cases though, a carefully chosen index will only be a benefit.

cbp
  • 25,252
  • 29
  • 125
  • 205
0

Not having indexes on id columns sounds really unusual and I would find any justification for not including them to smell very fishy.

You should be aware that if you are doing a high volume of commits to the database, adding more indexes will affect the speed of insertion, but no index on id? Wow.

It would be good to get better justification of exactly how adding extra indexes might cause problems though.

spender
  • 117,338
  • 33
  • 229
  • 351
0

the more indexes you have the slower data inserts and modifications will be. Make sure that you add indexes when appropriate and write queries that can take advantage of those indexes, also if the selectivity leve of your index is low, it will not be used effectively

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
0

I would say that if your server is having troubles with CPU time, indexes could be a solution. If you are querying tables without indexes, the server will need a lot more resources and if tables are having millions of records, it can become a serious problem. I recently cooled down a CPU from 80-90% all the time to 10-20% just by putting the right indexes. If using MS SQL, you could check the activity monitor to see what queries are expensive and create indexes based on the where clauses or joins.

enter image description here

Then at the recent expensive queries: enter image description here

You can then right click and check the complete query!

Mark
  • 379
  • 3
  • 7