Improving SQL Server Large Table Performance

Recently I encountered a table of astronomic size. The table is 27.7 GB with ~32.8 million rows. The table only had a single index and the query (below) fetches 4 columns using 3 aggregates filtering the last 6 months from GETDATE(). Below I will explain my methodology and how I was able to cut execution time by 36.996% and subtree cost by 84.703%.

SELECT id1, id2, MAX(desc1), COUNT(id1), MAX(col1) FROM table1
WHERE date1 >= DATEADD(mm, -6, GETDATE())
GROUP BY id1, id2

Initially a full table scan was performed because there was no useful index, then a sort. The full table scan cost 38% of the entire execution and the sort cost 59%. The remaining 3% was for parallelism and stream aggregation. Below are the results of execution stats for using a table scan.

Table Scan Performance

My initial reaction was to create an indexed view as this would create a physical result set in the database saving overhead. The subset created by the view was about 19.5 GB which is 70% of the base table (again, only concerned with the last 6 months). This was promising, however, when it came time to create an index on this view I ran into some problems. I attempted to create a non-clustered index on the view that was based on the resulting columns, however it required a clustered index first. I was unable to create a clustered index because the resulting subset did not contain any unique columns. This approach failed. In addition, there would be performance issues with this approach when performing CREATE, INSERT, or UPDATE requests.

I then opted to create a new non-clustered index on the base table. This allowed me to create a filter predicate to specify the last 6 months of data, however, without the non-deterministic function (GETDATE()) that was being used in the query. The last 6 months of data would now be hard coded into the index and I would force an index query hint in the query (...FROM table1 WITH (INDEX(idx)) WHERE date1 > 6 months ago). The index was based on the date, and included the resulting 4 columns. If you try this approach, make sure that any filter being used in the index matches your query.

While this was the optimal solution, it would require that the index be rebuilt every month to filter the last 6 months from the current date. The rebuilding wouldn’t save anytime overall, therefore the filter predicate was removed from the index. In addition, I used an index with included columns CREATE INDEX index1 ON table1 (col1) INCLUDE (col2, col3) versus CREATE INDEX index1 ON table1 (col1, col2, col3) because the included columns are at the leaf level (rather than in the tree) making the index smaller. As this MSDN article explains, only columns that are used for searching should be used as keys.

CREATE INDEX index1 ON table1 (date1)
INCLUDE (id1, id2, desc1, col1)

Index Scan Performance

With this approach, an index scan replaces the table scan and costs 9%, the sort 85% and 6% for parallelism and aggregation.

Table vs. Index Scan

In summary, the index scan cut total execution time by an average 366469 ms (▼ 36.996%) and cut wait time on server replies by an average of 379504 ms (▼ 74.1%).