3

I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the plan and see why it's more expensive.

I've removed everything from the query and I'm just selecting the column that is being filtered.

Index definition:

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

The query I'm trying to run is:

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT';

And SQL Server is returning the following error:

Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I've read a lot about the column filtered being included either in the key or as include column but nothing helps.

enter image description here

2
  • Is that the exact query (i.e. no parameters) and exact index definition? Feb 19, 2016 at 9:06
  • Hi there, yes! that's the exactly query i'm running. I had much more but i was getting that error so i removed every other part of the query so i could start from scratch but i'm getting the same error. The version is Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64) Apr 3 2015 14:50:02 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) . the table schema is the following : Full schema of the table and indexes can be seen here: tinypic.com/r/15qynbq/9 PS: the idx_all is filtered on the column I said
    – Tiago
    Feb 19, 2016 at 13:03

1 Answer 1

10

I can reproduce this in a database with PARAMETERIZATION FORCED.

CREATE DATABASE D1

ALTER DATABASE D1 SET PARAMETERIZATION FORCED

GO

USE D1

CREATE TABLE tbl_test
(
CommentDateTime DATETIME,
name VARCHAR(50),
comment VARCHAR(50),
CommentByType VARCHAR(10)
);

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT'
--OPTION (RECOMPILE)
;

The literal then gets parameterised and it is no longer guaranteed that the filtered index will match

where CommentByType = @0

In which case OPTION (RECOMPILE) allows the hint to succeed.

2
  • I think I do have forced PARAMETERIZATION on.. Is there any explanation why the query optimizer with forced parameterization would return an error ?Many thanks
    – Tiago
    Feb 19, 2016 at 13:04
  • 2
    @MrKudz - Because the parameterised plan with where CommentByType = @0 needs to work for any possible value of @0 - not just 'INT' so there is no guarantee that the filtered index will be suitable. i.e. SELECT CommentByType FROM tbl_test WITH (INDEX (idx_all)) WHERE CommentByType='SOMEOTHERVALUE' wouldn't work. Feb 19, 2016 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.