Thanks Bill for giving some really nice alternatives. For point 3 , we have already increased timeout to some 60 secs. I will try point 1,2 and 4.
POint 5 probably won't be possible.
Bill Talada (11/28/2016)
If your only problem is timeouts:
1. Preheat your data cache by doing a SELECT * FROM whatever WHERE checksum(*) = 1
2. Create an index before doing your SELECT and then drop it afterwards.
3. Change the default timeout for queries.
4. Only support the WHERE clause in the query, and do the COLUMNS, GROUP BY, and ORDER BY in the application instead of the database.
5. Require one column in the WHERE clause that is indexed such as a date column.
A) Item 1 is not helpful and will just be load on the system for nothing. You are already running queries that do table scans so whatever will fit in RAM will already be there.
B) Item 2 is FAR worse, because you have to do a table scan to get the data to create the index, then BUILD IT (sort in tempdb, IO/RAM/CPU BURN and a TABLE LOCK during all of this!!), THEN run the query, then DROP IT. Sorry, but that is just silly.
C) Item 3 should have been done long ago.
D) Item 4 is silly too. Do you really want to bundle up potentially 3.5 MILLION ROWS of 20 columns of data up into TDS packets, send them over the network and then have some now-CRUSHED system do grouping, aggregations, sorting, etc?
Things that WILL HELP:
E) BIGGER HARDWARE. 3.5M rows is NOT MUCH DATA unless the 20 cols are a bunch of blobs or massive character fields. RAM AND CPUs need to be chock full all the time. If the entire table doesn't fit in RAM (and RAM is 96GB or so meaning Standard Edition SQL Server 2012 gets all 64GB it can use) then you will need fast IO too. Tempdb will need to be ROBUST too since you may well be spilling work tables to there regularly.
F) Upgrade to SQL Server 2014 or even better 2016 and SP1. With both you can access 128GB RAM in SE SQL Server. With 2016 SP1 you can now have COMPRESSED TABLES or even better since this seems to be a reporting system COLUMN STORE INDEXES (in standard edition!!)!!
G) Use READ UNCOMMITTED isolation level so that no blocking occurs if data writes are happening at the same time as selects. Do note that this can lead to bad data.
H) Hire a performance tuning professional to review your hardware/virtualization/SQL Server/database/application/etc config/settings/mx/indexing/code/etc.
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service