[SQL Server] A column: to Index or not to Index

[oceanebelle]

So I have a table with 3 columns, that can have hundreds of millions of rows.

Now, I was wondering if removing 1 index from one column would impact the search time.

Basically the query is like

select * from mytable where col1 = somevalue and col2 = someothervalue

Currently, col1 and col2 has indices. Would it be alright to remove the index for col1?

Removing both indices is a no no since searches crawls when that is done.

Any enlightenment is highly appreciated.

[mendhak]

If the table will be read more than it will be written to, then it's a good idea to have an index on both. If there are going to be lots of UPDATEs and INSERTs, then the indices add an overhead (when updating).

In large tables, it's considered good practice to have a read-only version and a write-only version with replication between them; then have indices on the columns that are most often queried.

[oceanebelle]

the table is fairly read most of the time except for a few updates on a small number of rows from time to time.

[oceanebelle]

In large tables, it's considered good practice to have a read-only version and a write-only version with replication between them; then have indices on the columns that are most often queried.

I'm not a DB Admin but it'd be nice to know how that would be done.

[Pradeep1210]

Since the table has hundreds of millions of rows, and you are primarily using it for searching, you should not remove the indices.
Though those indices add some disk space overhead on the database, they are worth it, since disks space is now so cheap that it is hardly considerable. That disk space is nothing compared to the performance gain you get from it.

[mendhak]

Server1, DB1. Normal table, no index or minimal indices. Apps write to it.
Server2, DB1. Transactional replication from Server1 (so when a write occurs to Server1, it is sent to Server2). Apps use the read config string to read from Server2. Indices on Server2DB1. In your case, the index would be on col1, col2 assuming that that's the most common query against that table.

[oceanebelle]

yeah I think that's a great idea. Too bad i don't know how to setup hibernate like that aside from providing two different connections for the app at the same time.

Thanks for the info.

[FunkyDexter]

OB, just to be clear you ideally want one index that covers both columns rather than an separate index on each. I'm not sure if that will have been obvious from the other posts. If you use two separate indexes the system will use one of them when querying but won't gain a significant benefit from the second one.

Mendhak, have you got any sources on that? I've never come across it before and it raises some immediate questions for me. Like aren't you just moving the overhead onto Server2 rather than eliminating it? It sounds like a very useful technique, though, as I've got a couple of large tables that need to perform for both reads and writes. At the moment I just implement the indeces and sacrifice the write performance because it's less significant.

[abhijit]

OB, just to be clear you ideally want one index that covers both columns rather than an separate index on each. I'm not sure if that will have been obvious from the other posts. If you use two separate indexes the system will use one of them when querying but won't gain a significant benefit from the second one.

Mendhak, have you got any sources on that? I've never come across it before and it raises some immediate questions for me. Like aren't you just moving the overhead onto Server2 rather than eliminating it? It sounds like a very useful technique, though, as I've got a couple of large tables that need to perform for both reads and writes. At the moment I just implement the indexes and sacrifice the write performance because it's less significant.

We have a weekly batch job to rebuild the indexes on some of the tables.
If we don't run this job, we have seen performance getting slowed over a period of time. Another approach is to split the storage of the table.

If you want to read about partitioning the tables, this is a good link.

[oceanebelle]

OB, just to be clear you ideally want one index that covers both columns rather than an separate index on each. I'm not sure if that will have been obvious from the other posts. If you use two separate indexes the system will use one of them when querying but won't gain a significant benefit from the second one.

I could try creating an index that will have all columns, although that would probably not follow the Hibernate (A open-source persistence api) spec which is on a per field basis. Or maybe Hibernate supports this but I just don't know how. I'll try the one index for several columns and see how much growth the table experiences.

Another question, out of curiosity, how will SQL Server choose between two indices during a query?

[oceanebelle]

Any tips on what columns to group into 1 index?

[oceanebelle]

If I create an index with all columns in a table, performance is... hideous! but at least less hd usage.

if I create an index for each field, everything is a breeze! but of course 50%-80% increase in disk usage in my case.

So what's the rule for creating indexes? Do I join columsn that are almost always used in queries together? or does it have to do with the datatype? all numerics in one index and other types in another index?

[Pradeep1210]

Won't it be a bad idea to consider multi-column index where single column index is required?

[abhijit]

Won't it be a bad idea to consider multi-column index where single column index is required?

It is quite simple with regards to indexes, follow these two rules:

1) have as few indexes as possible
2) but as many as you need

[mendhak]

OB, just to be clear you ideally want one index that covers both columns rather than an separate index on each. I'm not sure if that will have been obvious from the other posts. If you use two separate indexes the system will use one of them when querying but won't gain a significant benefit from the second one.

Mendhak, have you got any sources on that? I've never come across it before and it raises some immediate questions for me. Like aren't you just moving the overhead onto Server2 rather than eliminating it? It sounds like a very useful technique, though, as I've got a couple of large tables that need to perform for both reads and writes. At the moment I just implement the indeces and sacrifice the write performance because it's less significant.

Sources? Of course not.

The scenario is that you have a database in which you write a bit, but read a lot. Like a news site. We publish an article 50 times a day but the database is read about x million times a day. Or, users post 30,000 times a day but the forum posts are read x million times a day. This required a separation with replication.

The web servers are load balanced, the SQL Servers are load balanced, web server talks to SQL Server, they're all subscribers to the main write-database publisher. If the write database fails, the read only databases are still around. There are multiple read-only databases as well, so they can fail too.

Yes, the overhead is being moved. You can't eliminate overhead, which is why it has to be mitigated. It slows the site down if the forum posts happen on the same database as the threads are read from, hence the separation.

[mendhak]

Also, I was talking about one index per column. If you index 3 columns in one index, then in the query, the first column of that compound-index is the most efficient, and then you get progressively bad performance for the second, worst for the third - this is because the compound index is ordered by the first column selected and then the second and then the third and so the records are not grouped together for column2 and column3.

However, it does help if your most common query involves "Where col1=a and col2=b" - that is, you have two columns which are almost always queried together. If it could be anything, anything at all, I'd use a single index for a single column.

[oceanebelle]

Won't it be a bad idea to consider multi-column index where single column index is required?

some columns are used with other columns in more scenarios but then those other columns are also used in other parts of the app which made the composite index a bad idea. Looking at it now, It's probably better to just leave it with 1 index per field. The indexed fields are necessary as the database grows but then there's the problem with the database size. I do like to keep things simple and I don't think there are ways to avoid the database size problem apart from taking the app back to the drawing board.

[Pradeep1210]

Just as I said in the earlier post, disk space should be less of a concern keeping in view that it is so cheap these days. You just need to put one additional disk if ever you run out of space on the current disk and it would take years to fill that one too.
The disk space affects just you, while performance affects your customers. You won't certainly let your customers have bad experience for just want of a new hard disk there.

However, if ever I need to cut down on space, I would consider removing redundant/old/archived tables or records for that thing rather than cutting down on indices. Maybe move them to a separate table or database so that I gain a large performance boost with the current ones.

The only situation where I would consider a multi-column index is where one field is dependent on the other and becomes meaningless without it. In all other situations I would rather prefer each column in consideration have a separate index of its own.

[FunkyDexter]

On the whole multi vs single column indeces thang: imagine a scenario with two fields (A and B) on a table.

If you have an index that covers just A then it will be useful for queries with a "Where A = SomeValue" because it can just seek the apropriate point on the index and work forward until it reaches the end of that data block on the index.

OK, now you need to query "Where A = SomeValue And B = SomeOtherValue". So you put a new index on B. When you issue that query it's going to use your first index to get all the records Where A = SomeOtherValue. But the second index is going to be nearly useless because the dataset now contains records that are fragmented across that inex. At best you'll get an index scan (which is little better than a table scan) across the second index but you won't be able to get an index seek, which is where the real benefit comes from. If you want to get the full benefit of an index seek for this query you need a single index that covers both A and B. Incidentally, the order of columns in the index doesn't have to match the order of columns in the query.

Now consider that you set an index up across both columns. For the multi column query that's great, everything's in the order that the query requires it so it can make full use of the index to do a seek. But what happenes when you have the query "Where A = SomeValue"? Assuming A is the first column on the index everythings all good. The records are in 'A' rder on the index so it can do a seek. But if A is not the first field than the index is useless, again because the records are fragmented across it. To get the benefit of both you need a multi-column index that supporte the multi column query and single column indexes that cover all the single fields you'll query against that aren't the first column in a multi column index.

You can actually take this a step further and create indexes that cover not just the fields you're filtering and orderung by but also teh fields your selecting. This means the query can get all the data it needs purely by checking the index - it never needs to touch the table at all. These are called covering indexes and are quite likely a step too far in your scenario.

So the upshot is, if you're ONLY concerned with read performance then it actually makes sense to create multi column indexes to suit every single query you'll ever run against the table. Of course, you're never only concerned with read performance, you're concerned with write performance too and every index you add affects write performance. As Pardeep said, I wouldn't get hung up on disk space, it's peanuts. Write performance is a valid concern however.

In the end I'd say any query you write that absolutly needs to perform needs an index supporting it and it should cover the columns it needs to cover. If you have a query that's called very frequently and the table is updated rarely, that's probably a good case for an index too. If you can make use of an existing index (eg there's a multi-column index which contains all the fields you're interested in at the front) then great, let the system use that rather than creating a new one. In the end, though, the whole things a balancing act.

Mendhak, I can certainly see a benefit of the replication aproach where robustness is concerned but I'm still unsure about performance. The subscribers still need to update their local indexes when the publisher sends it's update to them. The write on the publisher would be quicker because there's no indexes to maintain there but the reads coming off the subscribers would still be affected by the replicated update. I think I'm probably just failing to understand. I guess the replication effectively defers the overhead which would help balance the load, is that where the benefits coming from?

[mendhak]

Yes, the subscribers need to update the local indices when it receives the new rows of data - via a remote distributor - but the data propagated across doesn't have as much overhead as an INSERT on the principal database does when the transactional replication performs the INSERT - it has its own way of doing things. The reads coming off the subscribers are not affected by the replicated update as you'd think, that's where the Log Reader Agent comes in. There are also various things you can do to improve transactional replication performance.

Is that what you mean?

[FunkyDexter]

I've always assumed that a replicated insert would be handled exactly the same way as a straightforward insert. If that's not the case then that would seem to be where you'd get the benefit. I'll have to have a read up on the Log Reader Agent and your link. Thanks.