SQL Server Performance Tuning: I recently did some tuning work on a query that has computed columns in the SELECT. While the index that was being used by the query did contain columns used in the query's WHERE clause, performance was still not as fast as it could be. I checked the execution plan and could see that there were some parallelism operators, a non-clustered index scan with a key lookup on some computed columns that are part of the SELECT, but not in the WHERE clause. After adding the computed columns as included columns to the index being used, the parallelism and key lookup went away and the query really sped up.
CREATE NONCLUSTERED INDEX [ix_SQLRX_IncludeColIndex] ON [dbo].[Rebate]
([BranchId],[SupplierId],[ItemId],[ReceiveDt])
INCLUDE ([OnHandQty],[TotCostAmt],[PayDiscAmt],[EOMAmt],[EOQAmt],[EOYAmt],[RebateAmt])
ON [PRIMARY]
GO
More information from Microsoft here: https://msdn.microsoft.com/en-us/library/ms190806.aspx & https://msdn.microsoft.com/en-us/library/ms189292.aspx
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|
