1

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.

  • You would never get around with a "universal" Index that fits all your queries. You should identify which are important and expensive (interms of IO and CPU time), so that you can create proper indexes and possibly rewrite them. Also, since you are on sql 2012 and if you are using AlwaysON, you can redirect the readonly SELECT queries to the readable secondary to offload the load from Primary. – Kin Shah Aug 31 '15 at 19:22
  • @kin thanks for your comment. I understand that it is not quite possible and my old fellow developers did not arrange that well. I am just seeking a shortcut to cut down performance issues for few more months. – Zerotoinfinity Aug 31 '15 at 19:35
  • I agree with Kin, prioritize. Also, look into columnstore indexes, in the most recent versions of SQL Server they are writable and can be used precisely to solve this kind of problem. (Though, to be honest, none of these queries should really be a problem against 5 million rows, so maybe there are hardware limitations that are causing the issues. What are slow queries typically waiting on?) – Aaron Bertrand Aug 31 '15 at 19:52
  • @AaronBertrand I badly need SS2016 and I am able to convince my management to buy enterprise edition as soon as major release happen. I showed that editable columstore indexes, temporal tables, query store etc.. and they were impressed. Meanwhile, I need to find some alternate to fix it up. – Zerotoinfinity Aug 31 '15 at 19:57
  • 1
    @Zerotoinfinity I have it turned it on in my environment and no problems at all, but your environment is different than mine :-) .. so YMMW. It partitions the memory based on CUP and NUMA nodes which increases concurrency and performance. Also, for MAXDOP setting, you can use the script from here – Kin Shah Aug 31 '15 at 20:40
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

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.

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.