Wednesday, October 14, 2009

Path Menu Picture


Friday, October 2, 2009

StackOverflow Code for Question: T/F: Using IF statements in a procedure produces multiple plans (2)

This is code for the StackOverflow question here. This is an update to the code posted here.

SET NOCOUNT ON
GO

--<< Create table tblTag1
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblTag1')
    DROP TABLE dbo.tblTag1
GO
-- Table #1, tblTag1
CREATE TABLE dbo.tblTag1 (
    id          int                 IDENTITY,
    CreateDate  datetime            NOT NULL        CONSTRAINT DF_tblTag1_createDate  DEFAULT (GETDATE()),
    someTag     varchar(100)        NOT NULL,
    CONSTRAINT PK_tblTag1 PRIMARY KEY CLUSTERED (id)
)
GO

--<< Create table tblTagWithGUID
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblTagWithGUID')
    DROP TABLE dbo.tblTagWithGUID
GO
-- Table #2, tblTagWithGUID
CREATE TABLE dbo.tblTagWithGUID (
    id          int                 IDENTITY,
    CreateDate  datetime            NOT NULL        CONSTRAINT DF_tblTagWithGUID_createDate DEFAULT (getdate()),
    someTag     varchar(100)        NOT NULL,
    someGUID    uniqueidentifier    NOT NULL        CONSTRAINT DF_tblTagWithGUID_someGUID   DEFAULT (newid()),
    CONSTRAINT PK_tblTagWithGUID PRIMARY KEY CLUSTERED (id)
)
GO

--<< Load data
INSERT INTO dbo.tblTag1        (someTag) VALUES ('this')
INSERT INTO dbo.tblTag1        (someTag) VALUES ('that')
INSERT INTO dbo.tblTag1        (someTag) VALUES ('the other thing')

INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('this')
INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('that')
INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('the other thing')
GO

--<< Read stored procedure
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'spLoadTags')
    DROP PROCEDURE dbo.spLoadTags
GO
CREATE PROCEDURE dbo.spLoadTags (
    @Pick   tinyint = NULL
)
AS BEGIN
    SET NOCOUNT ON

    --<< Schema-qualified tblTag1
    IF @Pick = 0 BEGIN
        SELECT id, createDate, someTag FROM dbo.tblTag1
    END
    --<< Schema-qualified tblTagWithGUID
    ELSE IF @Pick = 1 BEGIN
        SELECT id, createDate, someTag FROM dbo.tblTagWithGUID
    END
    --<< Non-qualified tblTag1
    ELSE IF @Pick = 2 BEGIN
        SELECT id, createDate, someTag FROM tblTag1
    END
    --<< Non-qualified tblTagWithGUID
    ELSE IF @Pick = 3 BEGIN
        SELECT id, createDate, someTag FROM tblTagWithGUID
    END
END
GO

--<< Clear the procedure cache
DBCC FREEPROCCACHE
GO

--<< Multiple calls with different input values
DECLARE @ct     int
DECLARE @pick   tinyint

SET @ct = 0
WHILE @ct < 12 BEGIN
    SET @pick   = @ct % 4
    SET @ct     = @ct + 1

    EXEC dbo.spLoadTags @Pick = @pick
END
GO

--<< Get plan information
WITH PlanData AS (
    SELECT
        (SELECT SUBSTRING([text], statement_start_offset / 2 + 1,
            (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(MAX), [text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2)
        FROM sys.dm_exec_sql_text(sql_handle)
        WHERE [text] like '%tblTag%') AS query_text,
        plan_handle
    FROM sys.dm_exec_query_stats
)
SELECT DISTINCT
    execution_count,
    sys.dm_exec_query_stats.plan_handle,
    PlanData.query_text
FROM sys.dm_exec_query_stats
JOIN PlanData ON sys.dm_exec_query_stats.plan_handle = PlanData.plan_handle
WHERE PlanData.query_text IS NOT NULL
ORDER BY execution_count DESC
GO

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--<< RESULTS
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--execution_count plan_handle                                        query_text
----------------- -------------------------------------------------- ------------------------------------------------------
--              3 0x050013006D9E2E1FB8A0DB08000000000000000000000000 SELECT id, createDate, someTag FROM dbo.tblTag1
--              3 0x050013006D9E2E1FB8A0DB08000000000000000000000000 SELECT id, createDate, someTag FROM dbo.tblTagWithGUID
--              3 0x050013006D9E2E1FB8A0DB08000000000000000000000000 SELECT id, createDate, someTag FROM tblTag1
--              3 0x050013006D9E2E1FB8A0DB08000000000000000000000000 SELECT id, createDate, someTag FROM tblTagWithGUID

StackOverflow Code for Question: T/F: Using IF statements in a procedure produces multiple plans

This is code for the StackOverflow question here.

SET NOCOUNT ON
GO

--<< Create table tblTag1
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblTag1')
    DROP TABLE dbo.tblTag1
GO
-- Table #1, tblTag1
CREATE TABLE dbo.tblTag1 (
    id          int                 IDENTITY,
    CreateDate  datetime            NOT NULL        CONSTRAINT DF_tblTag1_createDate  DEFAULT (GETDATE()),
    someTag     varchar(100)        NOT NULL,
    CONSTRAINT PK_tblTag1 PRIMARY KEY CLUSTERED (id)
)
GO

--<< Create table tblTagWithGUID
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tblTagWithGUID')
    DROP TABLE dbo.tblTagWithGUID
GO
-- Table #2, tblTagWithGUID
CREATE TABLE dbo.tblTagWithGUID (
    id          int                 IDENTITY,
    CreateDate  datetime            NOT NULL        CONSTRAINT DF_tblTagWithGUID_createDate DEFAULT (getdate()),
    someTag     varchar(100)        NOT NULL,
    someGUID    uniqueidentifier    NOT NULL        CONSTRAINT DF_tblTagWithGUID_someGUID   DEFAULT (newid()),
    CONSTRAINT PK_tblTagWithGUID PRIMARY KEY CLUSTERED (id)
)
GO

--<< Load data
INSERT INTO dbo.tblTag1        (someTag) VALUES ('this')
INSERT INTO dbo.tblTag1        (someTag) VALUES ('that')
INSERT INTO dbo.tblTag1        (someTag) VALUES ('the other thing')

INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('this')
INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('that')
INSERT INTO dbo.tblTagWithGUID (someTag) VALUES ('the other thing')
GO

--<< Read stored procedure
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'spLoadTags')
    DROP PROCEDURE dbo.spLoadTags
GO
CREATE PROCEDURE dbo.spLoadTags (
    @Pick AS BIT = NULL
)
AS BEGIN
    SET NOCOUNT ON

    IF @Pick = 0 BEGIN
        SELECT id, createDate, someTag FROM dbo.tblTag1
    END

    IF @Pick = 1 BEGIN
        SELECT id, createDate, someTag FROM dbo.tblTagWithGUID
    END
END
GO

--<< Clear the procedure cache
DBCC FREEPROCCACHE
GO

--<< Multiple calls with different input values
DECLARE @ct     int
DECLARE @pick   bit

SET @ct = 0
WHILE @ct < 10 BEGIN
    SET @ct = @ct + 1
    SET @pick = (@ct % 2)

    EXEC dbo.spLoadTags @Pick = @pick
END
GO

--<< Get plan information
WITH PlanData AS (
    SELECT
        (SELECT SUBSTRING([text], statement_start_offset / 2 + 1,
            (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(MAX), [text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2)
        FROM sys.dm_exec_sql_text(sql_handle)
        WHERE [text] like '%tblTag%') AS query_text,
        plan_handle
    FROM sys.dm_exec_query_stats
)
SELECT DISTINCT
    execution_count,
    sys.dm_exec_query_stats.plan_handle,
    PlanData.query_text
FROM sys.dm_exec_query_stats
JOIN PlanData ON sys.dm_exec_query_stats.plan_handle = PlanData.plan_handle
WHERE PlanData.query_text IS NOT NULL
ORDER BY execution_count DESC
GO



----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--<< RESULTS
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- id createDate              someTag
----- ----------------------- ---------------
--  1 2009-10-02 11:13:03.020 this
--  2 2009-10-02 11:13:03.020 that
--  3 2009-10-02 11:13:03.020 the other thing

-- [output repeated]

-- execution_count plan_handle                    query_text
------------------ ------------------------------ ------------------------------------------------------
--               5 0x050013009D4CE352B8C0E2080... SELECT id, createDate, someTag FROM dbo.tblTag1
--               5 0x050013009D4CE352B8C0E2080... SELECT id, createDate, someTag FROM dbo.tblTagWithGUID