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.

Zerotoinfinity
  • 761
  • 3
  • 13
  • 27
  • 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
  • @aaronBertrand I have ample amount of hardware and 1TB of RAM with 80 logical CPU. I had CXPACKET waits and TempDb contention which I resolved by setting up MAXDOP & TempDb correctly. All of these application access that table at particular time. Queries which I mentioned in my question are just an example; actually they are quite complex with lookups to other tables too. – Zerotoinfinity Aug 31 '15 at 20:00
  • There's no magic here that will make all queries fast - you can put 100 indexes on the table, and that will make a good set of queries fast, but it will eat up disk space, cost you in terms of maintenance, and drastically affect the write portion of your workload. Indexing is largely about balance and trade-offs. If these tables are mostly read-only, then either (a) you can add more indexes or (b) you can consider using the 2012 implementation of columnstore, where you defer changes to maintenance periods. – Aaron Bertrand Aug 31 '15 at 20:06
  • Also, how did you "set MAXDOP correctly"? I hope you did not use the user-hammer of instance-wide MAXDOP 1. – Aaron Bertrand Aug 31 '15 at 20:07
  • @Zerotoinfinity since you are have a large number of CPUs, are you having Trace Flag - T8048 turned ON. Refer to Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads for some knobs to TUNE for your workload that will help you. – Kin Shah Aug 31 '15 at 20:18
  • @AaronBertrand :) I did not just set MAXDOP randomly. I may be wrong but I understand that MAXDOP 1 would be for Sharepoint etc because it will suppress parallel plan. I knew that hyperthreading is enabled and with few calculation I set it 6 then after watching performance and query plan I change it to 5 (Server level change). Since then things are quite good. :) Correct me if I am wrong anywhere. – Zerotoinfinity Aug 31 '15 at 20:26
  • MAXDOP 5? That is pretty random. Typically, MAXDOP should be divisible by 2, and/or equal to the maximum number of physical cores in a NUMA node. An odd MAXDOP is, well, odd. – Aaron Bertrand Aug 31 '15 at 20:27
  • I have 8 processor, so I started with 8. I did consider three factors while calculating it 1: Hyperthreading 2: Number of NUMA nodes 3: Logical CPU. I am scared now, have I done anything wrong? – Zerotoinfinity Aug 31 '15 at 20:32
  • @Kin Thanks for sharing the link, it is very informative. I have a question though, is T8048 safe for production? – Zerotoinfinity Aug 31 '15 at 20:33
  • 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

2 Answers2

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.

Jools
  • 47
  • 2
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: Link

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Ranvijay
  • 130
  • 3