0

I am using SQL Server 2016-SP1-CU8

Here is the code to setup my scenario:

    --1. Drop indexed views (in case of re-running
    IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
    IF OBJECT_ID('Widgets_2', 'V') IS NOT NULL DROP VIEW Widgets_2
    IF OBJECT_ID('Widgets_3', 'V') IS NOT NULL DROP VIEW Widgets_3
    GO

    --2. Setup table and data
    IF OBJECT_ID('Widgets', 'U') IS NOT NULL DROP TABLE Widgets
    CREATE TABLE Widgets (
        ID INT IDENTITY (1,1),
        WidgetName VARCHAR(128),
        WidgetTypeID TINYINT
    )

    INSERT dbo.Widgets
    (
        WidgetName,
        WidgetTypeID
    )
    SELECT TOP (100000)
        a.[name],
        a.[object_id] % 3
    FROM sys.columns a
    CROSS JOIN sys.columns b

    -- See what we have populated
    SELECT WidgetTypeID, COUNT(1) cnt
    FROM dbo.Widgets
    GROUP BY WidgetTypeID
    GO

    --3. Create indexed views
    IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
    GO
    CREATE VIEW Widgets_1
    WITH SCHEMABINDING
    AS 
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 1
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_1 (ID)
    GO

    IF OBJECT_ID('Widgets_2', 'V') IS NOT NULL DROP VIEW Widgets_2
    GO
    CREATE VIEW Widgets_2
    WITH SCHEMABINDING
    AS 
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 2
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_2 (ID)
    GO

    IF OBJECT_ID('Widgets_3', 'V') IS NOT NULL DROP VIEW Widgets_3
    GO
    CREATE VIEW Widgets_3
    WITH SCHEMABINDING
    AS 
    SELECT ID,
           WidgetName
    FROM dbo.Widgets
    WHERE WidgetTypeID = 3
    GO
    CREATE UNIQUE CLUSTERED INDEX UCDX_ID ON Widgets_3 (ID)
    GO

    --4. Create function using indexed views
    IF OBJECT_ID('udf_Get_Widgets', 'IF') IS NOT NULL DROP FUNCTION udf_Get_Widgets
    GO
    CREATE FUNCTION dbo.udf_Get_Widgets
    (
        @WidgetTypeID TINYINT
    )
    RETURNS TABLE
    AS
    RETURN
        SELECT 
            ID,
            WidgetName
        FROM dbo.Widgets_1 WITH (NOEXPAND)
        WHERE 1 = @WidgetTypeID
        UNION ALL
        SELECT 
            ID,
            WidgetName
        FROM dbo.Widgets_2 WITH (NOEXPAND)
        WHERE 2 = @WidgetTypeID
        UNION ALL
        SELECT 
            ID,
            WidgetName
        FROM dbo.Widgets_3 WITH (NOEXPAND)
        WHERE 3 = @WidgetTypeID
    GO

And here is my question. If I query the function using hard-coded parameter value like so:

    SELECT  ID,
            WidgetName
    FROM    dbo.udf_Get_Widgets (2)

I can see that only one of the underlying indexed views is utilized - this is what I would expect:

One indexed view is utilized

However, if I now run a query with a parameter:

    DECLARE @WidgetTypeID TINYINT = 2
    SELECT  ID,
            WidgetName
    FROM    dbo.udf_Get_Widgets (@WidgetTypeID)

I can see that all of the indexed views are accessed, even though two of them are unnecessary:

All of the indexed views are utilized

In real-life scenario, these could be very large tables with millions of rows and I am concerned that this will kill my performance in production. Should I be worried? BTW, all of this acrobatics is done as part of performance tuning exercise for EF queries, so I am prevented from using many of the normal performance tuning techniques.

  • 2
    That's generally how local variables behave. – Erik Darling May 23 '18 at 20:59
  • 1
    do the filters have startup predicates? I imagine so. In that case only one branch will be actually executed but cardinality estimates upstream probably wont be as good. – Martin Smith May 23 '18 at 21:15
  • Thank you, Martin Smith and Eric! I had to go and read about "startup expression predicates" because I've never even heard of those. I do see them in the execution plan. I further verified from STATISTICS IO that even though all of the views show up in the execution plan, they don't actually get read. Here is the good article on this written by Eric Darling: brentozar.com/archive/2017/04/… – SQL_Guy May 23 '18 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.