In this video we cover the two options available under parameterization in MSSQL database properties. Ad hoc queries are queries that aren’t stored procedures. Since the code is ad-hoc in nature we see that the WHERE condition often has hard coded values. Each time the hard coded value changes we see a new execution plan created since the body of the SQL text has changed resulting in a different checksum and the new checksum will be different from the previous SQL text even though the majority of the code is the same. The difference in checksum values prevent SQL server from reusing the prior execution plan. Naturally we can see how this will result in a large number of single use plans that eat up CPU during complication and RAM post execution.
One way to fix the above issue is to force developers to always query using stored procedures. But this might not be a solution for companies using ORM tools. In such cases the DBA is provided with a quick and easy way to force SQL server to substitute hardcodes values in SQL queries with variables (which behave much like input parameters in stored procedures). By replacing these values with “parameters” we can able to ensure the body of the SQL text doesn’t change between executions and so plans get reused. Improving overall performance.
I typically prefer to stick to stored procedures as much as possible and use Forced only when I know that the majority of the queries being executed as from ORM tools.
Script used in the video
/**********
Free procedure cachce
*************/
DBCC FREEPROCCACHE
DBCC PROCCACHE
/**********
CHECK procedure cachce
*************/
SELECT cacheobjtype
,objtype
,sum(usecounts)
,sum(size_in_bytes)
FROM sys.dm_exec_cached_plans
WHERE plan_handle IN (
SELECT plan_handle
FROM sys.dm_exec_query_stats q
OUTER APPLY sys.dm_exec_sql_text(q.sql_handle) t
WHERE t.TEXT LIKE '%jayanth%'
)
GROUP BY cacheobjtype
,objtype
GO
/**********
Set parameterization
*************/
ALTER DATABASE [AirlinePerformanceTuningDB]
SET PARAMETERIZATION FORCED
WITH NO_WAIT
GO
/**********
Run test queries
*************/
SELECT StateName AS [Jayanth]
,cast(b.FlightDate AS DATE) AS Fd
,sum(b.Price)
FROM Airports a
LEFT OUTER JOIN Bookings b ON a.AirportId = b.OriginAirportID
INNER JOIN Roster r ON r.DestAirportID = 2
WHERE AirportId = 2
AND getdate() BETWEEN CreateDate
AND UpdateDate
GROUP BY StateName
,cast(b.FlightDate AS DATE)
ORDER BY CASE
WHEN StateName LIKE '%a%'
THEN StateName
ELSE cast(b.FlightDate AS DATE)
END
GO
SELECT *
,StateName AS 'Jayanth'
FROM Airports
WHERE AirportId = 2
GO
/**********
Run test queries with different values
*************/
SELECT StateName AS [Jayanth]
,cast(b.FlightDate AS DATE) AS Fd
,sum(b.Price)
FROM Airports a
LEFT OUTER JOIN Bookings b ON a.AirportId = b.OriginAirportID
INNER JOIN Roster r ON r.DestAirportID = '3a'
WHERE AirportId = 3
AND getdate() BETWEEN CreateDate
AND UpdateDate
GROUP BY StateName
,cast(b.FlightDate AS DATE)
ORDER BY CASE
WHEN StateName LIKE '%a%'
THEN StateName
ELSE cast(b.FlightDate AS DATE)
END
GO
SELECT *
,StateName AS 'Jayanth'
FROM Airports
WHERE AirportId = 3
GO
/**********
Check Procedure cache
*************/
SELECT cacheobjtype
,objtype
,sum(usecounts)
,sum(size_in_bytes)
FROM sys.dm_exec_cached_plans p
WHERE plan_handle IN (
SELECT plan_handle
FROM sys.dm_exec_query_stats q
OUTER APPLY sys.dm_exec_sql_text(q.sql_handle) t
WHERE t.TEXT NOT LIKE '%sys%'
)
GROUP BY cacheobjtype
,objtype
/**********
Check the execution plan
*************/
SELECT *
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) qp
WHERE plan_handle IN (
SELECT plan_handle
FROM sys.dm_exec_query_stats q
OUTER APPLY sys.dm_exec_sql_text(q.sql_handle) t
WHERE t.TEXT NOT LIKE '%sys%'
)
AND dbid = 8
Please Consider Subscribing
