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 |
|
aclarke
Posting Yak Master
133 Posts |
 Posted - 2005-07-27 : 18:32:28
|
| Hello. Let's say I have a simple query like this one:select x1, x2, x3from sometablewhere x1 < 1000and x2 > 100order by x1OK, I could create an index on x1 and another index on x2. I could also create an index on both (x1,x2). I'm trying to figure out when I would create indexes on single columns and indexes on multiple columns. I can't find any good information online about this.It SEEMS like in this case I could create an index on (x1,x2) since they're both in the where clause, but then I'd also have to create an index on x1 separately since it's in the order by. Is it more inefficient to just create a separate index for each column that needs to be indexed? That's what I usually do, but I figure it's time to learn when and why to use single indexes on multiple columns.Thanks,- Andrew. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 02:41:15
|
| I reckon the answer will be "it depends"! Tests on real data are probably your best approach.I'm not sure how useful an index on x1, x2 would be for this - ploughing through all the data from "x1 < 1000" to then check "x2 > 100" seems like hard work - compared to merging two index resultsets separately - but "it all depends" on the data mix and so on.If you had an index on x1, x2, x3 this particular query would be "covered" by the index, and wouldn't need to go to the data at all.Kristen |
 |
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-07-28 : 07:54:34
|
| >>It SEEMS like in this case I could create an index on (x1,x2) since they're both in the where clause,right, most likely you need composite index x1+x2,(actually +x3 to create covered index as Kristen said) but not only because they are in WHERE clause but also because there is AND keyword. Imagin you haveselect x1, x2, x3from sometablewhere x1 < 1000OR x2 > 100in this case you would need 2 indexes (x1 and x2) because there is no help from composite index - after checking x1 value MSSQL will need to scan whole table to check for x2 |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2005-07-28 : 10:29:47
|
| Thank you for your responses. For clarification, why would I need to index x3 in this case? It's not being used as a criterion for limiting or ordering the results, so I'm not sure why it would need to be indexed.Also, if I made one index for x1 and one for x2, when I look at the execution plan for queries like this, it seems like those indexes are used. So is it really usually more efficient to have one index for (x1,x2) instead of two separate indexes? I ask this because it seems like in most cases you'd need the separate indexes anyway for other queries that use the data, and then suddenly you have a whole lot of indexes eating up your RAM!Thanks again,- Andrew. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 10:52:04
|
To "cover" a query all columns used in the WHERE and SELECT etc. need to be in the index - then SQL doesn't need to bother to go to the actual data record. Very specific situation, probably not relevant to your case (assuming you have a cvariety of general queries rather than this very specific example alone)An index for x1, x2 will probably help this particular case (but it might not if x1 is very selective and x2 is not). However, as you summise, an x1, x2 index is unlikely to help another query which only querries on x2. So in general I would have two separate indexes.But as I said before the only real way to know is to have some representative data and test it!If you only have an index on x1, and its reasonably selective, the absence of an index on x2 may not make much difference to query speed.I wouldn't worry about RAM a) you should have bucket loads of that anyway for SQL to run well  and b) the thing to worry more about is whether the indexes will have to be updated a lot - if you have lots of UPDATEs on the data, relative to SELECTs, then SQL will have to work quite hard to change the indexes each time. If you do SELECTs for, say, hundreds of times relative to each UPDATE then the more indexes the merrier - in general - but even then Testing would be prudent!Kristen |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-07-28 : 10:56:36
|
| it's not so easy to come up with the ideal index set for complex database with hungreds of queries... Thats why Microsoft made Index Tuning Wizard, they say it helps in difficult situations... |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2005-07-28 : 11:28:17
|
| Thanks again for your responses. I didn't realize that it made any sense to index columns that were only in the select but weren't in the order, where, group or having clauses.Also, I can't use the index tuning wizard on this particular database as I don't have rights to the statistics. I'm trying to work off the execution plan and general knowledge. Some of the tables I'm working on have upwards of 80 million rows and are inserted into every second or so. All in all the database is quite large (151GB) so, like most people, have to balance my needs against everyone else's, and I don't currently have the freedom to move some of these reporting tasks onto a separate server where they belong.- Andrew. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 11:38:20
|
Here's what I normally do around snippets of queries that I want to do performance tests onDBCC DROPCLEANBUFFERS -- Do NOT use on production serversDBCC FREEPROCCACHEGO-- SET SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON... put query here-- SET STATISTICS IO OFF; SET STATISTICS TIME OFFGO-- SET SET SHOWPLAN_TEXT OFFGO I comment-in the commented-out bits as appropriate and try to get the LOGICAL READS figure lower - by modifing the query and/or indexes.Kristen |
|
|
|
|
|
|
|
|
Indexes on multiple columns