CHAPTER 28 ■ PERFORMANCE TUNING
How It Works
This recipe gave a brief introduction on how to use the Database Engine Tuning Advisor to make index recommendations based on a Transact-SQL query. You can use this tool to evaluate and make recommendations based on a query workload.
As a best practice, always be sure to evaluate the benefit of faster query time versus the disk space and data modification overhead. You should also check the impact of any new indexes on the duration of existing queries.
Also, be sure to tune your database only during slow periods on your SQL Server instance. This is because the Database Engine Tuning Advisor can consume significant CPU and memory resources while analyzing a workload. To reduce unnecessary overhead of the tool, be sure to remove objects types that you don’t want recommendation on from the Tuning Options tab.
The next two recipes detail a few techniques which don’t cleanly fall under any of the previous sec- tions in this chapter. These recipes will demonstrate how to use an alternative to dynamic SQL and stored procedures using the sp_executesql system-stored procedure. The last recipe in the chapter will show you how to apply query hints to an existing query without having to actually modify the application’s SQL code using plan guides.
Using an Alternative to Dynamic SQL
Using the EXECUTE command, you can execute the contents of a character string within a batch, pro- cedure, or function. You can also abbreviate EXECUTE to EXEC. For example, the following statement performs a SELECT from the Sales.Currency table:
EXEC ('SELECT CurrencyCode FROM Sales.Currency')
Although this technique allows you to dynamically formulate strings which can then be exe- cuted, this technique comes with some major hazards.
The first and most important hazard is the risk of SQL injection. SQL injection occurs when harmful code is inserted into an existing SQL string prior to being executed on the SQL Server instance. Allowing user input into variables that are concatenated to a SQL string and then executed can cause all sorts of damage to your database (not to mention the potential privacy issues). The malicious code if executed under a context with sufficient permissions can drop tables, read sensi- tive data, or even shut down the SQL Server process.
The second issue with character string execution techniques is in their performance. Although performance of dynamically generated SQL may sometimes be fast, the query performance can also be unreliable. Unlike stored procedures, dynamically generated and regular ad hoc SQL batches and statements will cause SQL Server to generate a new execution plan each time they are run.
If stored procedures are not an option for your application, an alternative, the sp_executesql system-stored procedure, addresses the dynamic SQL performance issue by allowing you to create and use a reusable query execution plan where the only items that change are the query parameters. Parameters are also type-safe, meaning that you cannot use them to hold unintended data types. This is a worthy solution, when given a choice between ad hoc statements and stored procedures.
Caution sp_executesql addresses some performance issues, but does not entirely address the SQL injection
issue. Beware of allowing user-passed parameters that are concatenated into a SQL string! Stick with the parame- ter functionality described next.