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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment