May 3, 2012 at 12:00 pm
Hello,
I'm a bit confuse about index intersection.
MSDN says you can but:
- I'm not enable to reproduce the case example
- I've never sees anything like that (and even heard that's not supported)
http://msdn.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx
The FORCESEEK hint supports index unions and intersections. The hint makes the query optimizer more likely to use these techniques. To avoid slowing the compilation time of simple queries, index unions and intersections are normally only chosen according to rules that take into account the cardinality and selectivity of the columns. However, when the FORCESEEK hint is specified, such rules are bypassed and these techniques are always considered.
http://msdn.microsoft.com/en-us/library/ms188722%28v=sql.105%29.aspx
Do not use multiple aliases for a single table in the same query to simulate index intersection. This is no longer necessary because SQL Server automatically considers index intersection and can make use of multiple indexes on the same table in the same query.
Microsoft case test reproduced here which does not show any index intersection (under SS2k8R2 10.50.1617):
http://msdn.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx
CREATE TABLE [dbo].[intTable](
[int1] [int] NOT NULL,
[int2] [int] NOT NULL
)
GO
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[intTable]
(
[int1] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[intTable]
(
[int2] ASC
)
GO
DECLARE @i AS INT = 1
WHILE @i < 30000
BEGIN
INSERT INTO dbo.intTable VALUES (@i,@i+1)
SET @i += 1
END
GO
SELECT * FROM dbo.intTable T WITH(FORCESEEK) WHERE T.int1 = 1 AND T.int2 = 2;
There's even an article on SSC that promote this. However it's more 10 years old:
http://www.sqlservercentral.com/articles/Performance+Tuning/indexintersection/194/
What am I missing? Or what are the particular scenario to enable (make SQL use) index intersection?
Can someone provide a test sample that actually trigger index intersection (or show how to detect them in the execution plan)?
ty
May 3, 2012 at 12:21 pm
Index intersection is rare and hard to demo. I have a demo of it in my query tuning book, but it's pretty contrived. You won't see it in the wild much at all. My example used AdventureWorks.
SELECT soh.*
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesPersonID = 276
AND soh.OrderDate BETWEEN '4/1/2002' and '7/1/2002'
This will get you a plan with a clustered index scan. If you create an index:
CREATE NONCLUSTERED INDEX IX_Test ON Sales.SalesOrderHeader(OrderDate)
Then run the query again, you should see a Hash Join operator putting together two index operations. That's index intersection. I have different values for the 2012 version of the book coming out soon.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2012 at 12:31 pm
Intersections demoed and discussed here: http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/
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 3, 2012 at 12:54 pm
Ty Grant for the example, I'll test it on my box tonight and check the generated execution plan.
I have different values for the 2012 version of the book coming out soon.
Is this the book you're talking about: "Dissecting SQL Server Execution Plans"? Because I don't remember reading about index intersection in that book. (Yes I've already read it and thanks to you, I now know what are rebinds & and rewinds! (I've also remember other things don't worry :-D))
Gail, Thank you for the link I've read it and it's very detailed.
Yes I do fall into a scenario where index intersection would probably be the best choice and I would like to test that but I've no clue how to make it happen even when forcing SQL with hints. (Yes I know hint should be avoided but for testing an index it's very very useful!)
May 4, 2012 at 4:33 am
Glad the book is useful.
Not that one though. This one is out soon.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2012 at 6:06 am
Thanks Grant,
It works exactly as you described. It even works when using FORCESEEK (as it should as Microsoft stated)
It would be nice having an index hint that force index intersection. SQL Server 2016?
May 4, 2012 at 6:37 am
Megistal (5/4/2012)
Thanks Grant,It works exactly as you described. It even works when using FORCESEEK (as it should as Microsoft stated)
It would be nice having an index hint that force index intersection. SQL Server 2016?
Ha! I suspect not. The way I've heard people talk about optmization, I think this is considered a little bit of a side path and not one of the main reward points for optimization. If you benefit from it, great, but I wouldn't aim for it in general.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2012 at 6:51 am
Ahahah
Ha! I suspect not!
I know it won't but that's what dream are for right (and in color plz!) 😉
Ty for your time (and Gail) on this one.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply