Index intersection

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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!)

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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?

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply