Database Reference
In-Depth Information
For improved plan cache reusability, execute the dynamic SQL string as a parameterized query using
sp_executesql
.
DECLARE @n NVARCHAR(3) = '776',
@sql NVARCHAR(MAX),
@paramdef NVARCHAR(6);
SET @sql = 'SELECT * FROM Sales.SalesOrderDetail sod '
+ 'JOIN Sales.SalesOrderHeader soh '
+ 'ON sod.SalesOrderID=soh.SalesOrderID ' + 'WHERE sod.ProductID=@1' ;
SET @paramdef = N'@1 INT';
--Execute the dynamic query using sp_executesql system stored procedure
EXECUTE sp_executesql
@sql,
@paramdef,
@1 = @n;
Executing the query as an explicitly parameterized query using
sp_executesql
generates a parameterized plan
for the query and thereby increases the execution plan reusability.
Parameterize Variable Parts of Queries with Care
Be careful while converting variable parts of a query into parameters. The range of values for some variables may vary
so drastically that the execution plan for a certain range of values may not be suitable for the other values. This can
lead to bad parameter sniffing (covered in Chapter 16).
Do Not Allow Implicit Resolution of Objects in Queries
SQL Server allows multiple database objects with the same name to be created under different schemas. For example,
table
t1
can be created using two different schemas (
u1
and
u2
) under their individual ownership. The default owner
in most systems is
dbo
(database owner). If user
u1
executes the following query, then SQL Server first tries to find
whether table
t1
exists for user
u1
's default schema.
SELECT * FROM tl WHERE cl = 1;
If not, then it tries to find whether table
t1
exists for the
dbo
user. This implicit resolution allows user
u1
to create
another instance of table
t1
under a different schema and access it temporarily (using the same application code)
without affecting other users.
On a production database, I recommend using the schema owner and avoiding implicit resolution. If not, using
implicit resolution adds the following overhead on a production server:
•
It requires more time to identify the objects.
•
It decreases the effectiveness of plan cache reusability.
