How can't Optimize the query

Jose Velarde 2016-11-08 23:56:17

/storage/attachments/1778-Plan.pesession

Vlady Oselsky 2016-11-09 15:01:20

Have you looked at index fragmentation and have you tried to update statistics? Additionally, what is histogram saying for this index? The first thing I would try is to update statistics and second as a test run query with (FORCESCAN).

SQLkiwi 2016-11-16 06:51:06

The main thing would be to ensure that the columns used in the plan Filter predicates have suitable data types.

It seems (from the separate Filter operator) that the columns involved are currently typed as large objects (LOBs) e.g. varchar(max). Fix this by giving the columns a more suitable type (assuming the data involved will fit in <= 8000 bytes) and using the correct modelling hints (assuming you are using something like Entity Framework). With non-LOB types, the engine will be able to push the Filter conditions into the Index Seek, which can be made more efficient still by careful indexing (non-LOB columns can be index keys, LOB columns cannot). If the optimal plan shape for different variable values varies, adding an OPTION (RECOMPILE) hint to the query or using dynamic SQL may pay dividends as well. Using the recompile hint allows SQL Server to generate an optimal plan for the current variable values on each execution, at the cost of a recompilation.

November 8, 2016

No Comments