We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can select any number of columns, rows; they can filter by, group by, sort by and aggregate by any columns they want.

This table is actually 'precomputed' from a set of relational tables; this precomputed tables is 90 columns wide and will be used solely for the users' queries. This big flat table will be produced just once a day.

Because we have no idea in advance what they will actually be doing these operations against, we were suggested to place an index on every column of the table.

My question is, would this have any adverse effects?

We would also want this 'methodology' to extend to future datasets that we provide similar functionality on; 1.7 million rows with 120 columns

For reference, we are using SQL Server 2008 R2.

  • 2
    It is unlikely that an index on every individual column will really help that many queries. Unless they're only selecting THAT column by itself, the index might be used to find rows, but it will still have to perform a lookup for every row to get the other columns. Instead of just putting 90 indexes on the table, why not develop indexes once you've observed actual user behavior and see what kind of queries they're actually going to run (and which ones can be improved by specific, targeted indexes)? Jun 21 '14 at 11:18
  • That's indeed what we'd prefer; chicken-and-egg problem: we need to deploy it and observe usage, then we'll be able to optimize. But without optimizing, it's really slow. Also your statement seems correct, I'm not noticing a great difference in performance when indexing all 90 columns. As g2server says, could be a warehouse workload.
    – Mendhak
    Jun 21 '14 at 11:56
  • 1
    Can you describe the nature of these 90 columns (data types, what do they really mean, are they always, usually, seldom populated, etc.)? Is there a primary key? Is it clustered? What is the data type? Jun 21 '14 at 15:55
  • You say that indexing all 90 columns doesn't produce a noticeable performance difference. If the SELECT statements on the table filter on more than one column, e.g. there are AND/OR or other logical operators in the WHERE clause, you may need composite indices on the commonly-used filter columns to gain any performance. You should have an idea of the workload and types of queries to be used in production and be able to do some performance testing on a development/staging server.
    – dartonw
    Jun 22 '14 at 2:01

It sounds like this is more of a DataWarehouse workload than an OLTP. In that case, heavily indexed tables are normal.

Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it).

I know your using 2008R2 but FYI, I believe the new columnstore indexes in 2012 shine against this type of workload. If you can't get the performance you want out of 2008R2 consider testing with this index technology on 2012.

Finally, you could monitor the index usage for the first few days/weeks There are missing index scripts and unused index scripts, that can be run on this table once the user query pattern is determined.

  • Would this be a data warehouse workload even if it's just 250K rows? In the future we may go up to 1.5 or 3 million rows, but that's it. We have also been considering Redshift and SQL 2014 columnstore. The performance is great on Redshift but I wanted to exhaust SQL Server before suggesting it to my team.
    – Mendhak
    Jun 21 '14 at 11:58
  • 2
    We have found benefits using page compression in 2008R2 with larger tables, though not as good as columnstore in 2012. Jun 21 '14 at 12:00
  • 1
    @Mendhak, well, I guess the argument could be made, keeping in mind not just the projected row count but the width. it also seems write once (during the nightly load) read many.. another characteristic.
    – g2server
    Jun 21 '14 at 12:11
  • @g2server if you write once and read many, sure there is no harm in having the indexes built (other than the additional disk space they consume), but they don't really serve any purpose if they aren't useful in satisfying any queries. Jun 21 '14 at 15:54
  • @AaronBertrand, yes I agree. but those can be removed with unused index scripts & new ones added with the missing index scripts once the usage pattern becomes more clear. its a shame 2012 is not used, for this adhoc dw workload, because its a good candidate for the columnstore indexes.
    – g2server
    Jun 22 '14 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.