DB Options :- PARAMETERIZATION
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.
Need more information? Visit https://www.enabledbusinesssolutions.com
To know more about Jayanth Kurup visit http://www.jayanthkurup.com
Jayanth is a Microsoft SQL Server trainer / consultant with over 15 years’ experience. He has worked with companies like Microsoft, IBM, HCL, Wipro, Well Fargo, Zeomega and many more. He is based out of Bangalore and a very active member of the SQL Server Community.
In this channel he covers complex topics related MS SQL Server , PowerBI , Azure , AWS , Powershell, SSIS , SSAS , SSRS , Machine Learning , AI , etc
You can also reach Jayanth via his LinkedIn profile below / jayanth-k. .
music from HAAWK