locked
Filter index question RRS feed

  • Question

  • I am comming to this example

    https://www.mssqltips.com/sqlservertip/2353/performance-advantages-of-sql-server-filtered-statistics/

    CREATE TABLE MyRegionTable(id INT, Location NVARCHAR(100), USState CHAR(2))
    GO
    CREATE TABLE MySalesTable(id INT, detail INT, quantity INT)
    GO
    CREATE CLUSTERED INDEX IDX_d1 ON MyRegionTable(id)
    GO
    CREATE INDEX IDX_MyRegionTable_name ON MyRegionTable(Location)
    GO
    CREATE STATISTICS IDX_MyRegionTable_id_name ON MyRegionTable(id, Location)
    GO
    CREATE CLUSTERED INDEX IDX_MySalesTable_id_detail ON MySalesTable(id, detail)
    GO
    INSERT MyRegionTable VALUES(0, 'Atlanta', 'GA')
    INSERT MyRegionTable VALUES(1, 'San Francisco', 'CA')
    GO
    SET NOCOUNT ON
    -- MySalesTable will contain 1 row for Atlanta and 1000 rows for San Francisco
    INSERT MySalesTable VALUES(0, 0, 50)
    DECLARE @i INT
    SET
    @i = 1
    WHILE @i <= 1000 BEGIN
    INSERT
    MySalesTable VALUES (1, @i, @i*3)
    SET @i = @i + 1
    END
    GO
    UPDATE STATISTICS MyRegionTable WITH fullscan
    UPDATE STATISTICS MySalesTable WITH fullscan
    GO

    --- So when come into the following statement 

    SELECT detail FROM MyRegionTable JOIN MySalesTable ON MyRegionTable.id = MySalesTable.id
    WHERE
    Location='Atlanta' OPTION (recompile)

    so we know the estimation is wrong. But I am question why the join query doesn't consider the stats of 

    IDX_MySalesTable_id_detail?????? 

    Thursday, August 13, 2020 6:12 AM

All replies

  • I don't see any filtered index?

    As for the question, SQL Server would need more sophisticated statistics to be able to get this estimate correct. It does not know when compiling the query that Atlanta maps to 0 etc, so it just makes a general assumption based on the density in the table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 13, 2020 9:14 PM
  • technically, you should have showed in statistics for Location :  IDX_MyRegionTable_name


    jchang

    Thursday, August 13, 2020 11:29 PM
  • why not juse use Mysalestable.id as the predicate . ??? It show the stats correctly in the plan. 

    Tuesday, August 25, 2020 10:16 AM
  • only one has the idea ?

    Friday, August 28, 2020 8:36 AM