Index planning for many columns with different usage

Question

I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million records. Unfortunately, I can't change the architecture and for next few months I have to continue with this. Now, as I said there are multiple application connected to the table which are fetching data from it and almost all of them using different columns for their purpose. Example of few of those queries are:

SELECT Col1, Col2 
FROM Table
WHERE Col3 = 'something'

SELECT Col4, Col5, Col6
FROM Table
WHERE Col7 IN ('A','B') AND Col1 = 'something'


SELECT Col1, Col2, Col7, Col28, ....
FROM Table 
WHERE Col1 = 'Something' AND Col2 = 12 AND (Col2 > 2 OR Col7 <20)

As you can see that one column is being used in where clause and some query it is in select clause. I have created indexes and now I realized that I need more but that doesn't seems to be feasible to me as I will end up in having so many indexes.

How can I implement index strategy in this kind of scenario?

Also,

How to design index when those columns are coming up in different combinations?

Ex.:

SELECT Col1, Col2, Col3
FROM Table
WHERE Col3 = 'something' AND Col4 = 'something'

SELECT Col4,Col1, Col5
FROM table
WHERE Col3='Something'AND Col7 = 'something' AND Col67 = 'something'

out of 80 columns, application is using > 50 columns in where clause.

Answer 1

Of course, the indexes need to be prioritized. You can create only those indexes that would impact most number of users, or most critical users, or would have maximum impact on the system. Look at the following blog for an easy way to identify missing indexes: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Answer 2

If the only thing that inserts / updates data in this table is an out-of-hours process I'd be tempted to apply as many indexes as you can. 999 are allowed for a SQL Server 2012 table. You can determine how many you need using the Database Tuning Advisor.

Obviously this will slow down the load process and consume a lot more disk space - both from the indexes and the additional log data - but you can create a script to Disable the indexes prior to the load & Enable them afterwards to speed things up.

If disabling indexes isn't available to you you can create a script to drop & re-create them.