May 2, 2009 at 4:50 am
Hello everyone.
I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:
USE tempdb;
GO
IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO
CREATE TABLE Employees
(
ID INT NOT NULL IDENTITY CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED,
ManagerID INT NULL,
Department VARCHAR(2) NOT NULL
)
GO
CREATE INDEX IX_0 ON Employees ( Department )
WHERE ManagerID IS NULL
GO
WITH T AS
(
SELECT
ID
FROM Employees WITH ( INDEX = IX_0 )
WHERE ManagerID IS NULL
)
SELECT * FROM T
If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.
Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.
Is there some way to working around? Thank you.
May 2, 2009 at 5:50 am
Post the exec plan?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2009 at 8:37 pm
Heh... I'd recommend that you may need to actually put some data in the table before you even think of trying to optimize a query.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 7:49 pm
Here's the query plan.
That's quite obvious SQL Server doesn't leverage the knowledges of the index.
This query was abstracted from real word scenario, in which the table is loaded, but the query plan is as wrong as the simple one.
May 3, 2009 at 8:43 pm
In that case, I'd recommend that you remove the index hint and add the proper index as a covering index instead of what you currently have as IX_0. By "proper", I mean one that will support the WHERE clause you have.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 8:47 pm
Jeff Moden (5/3/2009)
In that case, I'd recommend that you remove the index hint and add the proper index as a covering index instead of what you currently have as IX_0. By "proper", I mean one that will support the WHERE clause you have.
Thank you for your suggestion.
But The IX_0 is exactly the PROPER and COVERING index to support my query.
( I removed all filters in the query except the filters used to define the index )
May 4, 2009 at 3:15 am
Can you post the execution plan please, not an image of it? I need to see the properties of all the operators.
Right click the plan, select save as. Save as a .sqlplan file, zip it and attach the zip file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 4:45 am
GilaMonster (5/4/2009)
Can you post the execution plan please, not an image of it? I need to see the properties of all the operators.Right click the plan, select save as. Save as a .sqlplan file, zip it and attach the zip file.
🙂 It's in the attachment.
May 4, 2009 at 11:52 am
Odd. Behaviour doesn't change even if there's a lot more rows added. I'm going to ask some other people, see if I can get an explanation.
The output list of the key lookup is blank, it's not looking anything up. I wonder if it's got to do with plan safety (safe to be reused)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 11:56 am
Sheng (5/3/2009)
Jeff Moden (5/3/2009)
In that case, I'd recommend that you remove the index hint and add the proper index as a covering index instead of what you currently have as IX_0. By "proper", I mean one that will support the WHERE clause you have.Thank you for your suggestion.
But The IX_0 is exactly the PROPER and COVERING index to support my query.
( I removed all filters in the query except the filters used to define the index )
Heh... Since it's not working, I'd have to say that's apparently not true. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 12:13 pm
No, it is covering and the optimiser should, theoretically, be picking it. Might be a bug, might be something subtle that we've missed.
If we flip the query and the index predicates around
CREATE INDEX IX_1 ON Employees ( Department )
WHERE ManagerID IS NOT NULL
GO
Then there's no key lookup. The optimiser is, however, still picking the clustered index scan by default, even though it's more expensive.
SELECT ID
FROM Employees
WHERE ManagerID IS NOT NULL
-- clustered index scan
Table 'Employees'. Scan count 1, logical reads 314
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 1141 ms.
SELECT ID
FROM Employees with (index= IX_1)
WHERE ManagerID IS NOT NULL
-- nonclustered index scan on the filtered index
Table 'Employees'. Scan count 1, logical reads 138
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 1065 ms.
If I go and create a nonfiltered nonclustered index, the optimiser selects that, but it's not as efficient, in terms of reads, as the filtered index.
CREATE INDEX idx_2 ON Employees ( ManagerID, Department )
GO
SELECT ID
FROM Employees
WHERE ManagerID IS NOT NULL
-- nonclustered index seek on the unfiltered index.
Table 'Employees'. Scan count 1, logical reads 211, physical reads 0.
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 871 ms.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2009 at 7:18 pm
GilaMonster (5/4/2009)
No, it is covering and the optimiser should, theoretically, be picking it. Might be a bug, might be something subtle that we've missed.If we flip the query and the index predicates around
Then there's no key lookup. The optimiser is, however, still picking the clustered index scan by default, even though it's more expensive.
I believe SQL Server choose to generate a trivial plan in this situation, so that may be the reason SQL Server use a clustered index scan.
And it seems SQL Serve prefer non-filtered index to filtered ones when at the at the first time it turns to.
But it's irrational when we HINT it ( actually, force it ) to use the filtered index, it use another KEY LOOKUP and INNER JOIN.
To whom have to face to the situation, my suggestion is to use a filtered view ( if it's possible ) and index it, then query against the view instead of the base tables.
Still be curious about it.:-)
May 4, 2009 at 10:23 pm
GilaMonster (5/4/2009)
No, it is covering and the optimiser should, theoretically, be picking it. Might be a bug, might be something subtle that we've missed.
I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index on the Department column even though there's a filter on a column that happens to be used in the query. The following produces a very nice index seek instead of a lookup.
[font="Courier New"]USE tempdb;
GO
CREATE TABLE Employees
(
ID INT NOT NULL IDENTITY,
ManagerID INT NULL,
Department VARCHAR(2) NOT NULL
)
GO
CREATE CLUSTERED INDEX PK_Employee ON Employees ( ID )
GO
CREATE INDEX IX_0 ON Employees ( managerID )
GO
WITH T AS
(
SELECT
ID
FROM Employees WITH ( INDEX = IX_0 )
WHERE ManagerID IS NULL
)
SELECT * FROM T
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2009 at 1:34 am
Jeff Moden (5/4/2009)
I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index on the Department column even though there's a filter on a column that happens to be used in the query. The following produces a very nice index seek instead of a lookup.
Thank you for your code, but I think you're wrong on this.
Although it's an index on the department column, it's still a covering index on the query. Because there's a clustered index on it, and the ID column is the WHOLE key.
Your code is fine, but please notice the title of this subject, it's about the filtered index with IS NULL predicate.
May 5, 2009 at 1:42 am
Jeff Moden (5/4/2009)
I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index on the Department column even though there's a filter on a column that happens to be used in the query.
From Books Online: (http://msdn.microsoft.com/en-us/library/cc280372.aspx)
In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition.
A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDateON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
That is precisely the case here. The managerID is only used in the where clause and the filtered index expression is equivalent to the query predicate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply