5. ESTUDIO DE ALTERNATIVAS
5.1. Alternativas verano
As was discussed at the beginning of the chapter, troubleshooting poor query performance involves reviewing many areas such as database design, indexing, and query construction. You can make modifications to your code, but what if the problem is with code that you cannot change? If you are encountering issues with a database and/or queries that are not your own to change (in shrink- wrapped software, for example)—then your options become more limited. Usually in the case of third party software, you are restricted to adding new indexes or archiving off data from large tables. Making changes to the vendor’s actual database objects or queries is usually off limits.
SQL Server 2005 provides a new solution to this common issue using plan guides. Plan guides allow you to apply hints to a query without having to change the actual query text sent from the application. Plan guides can be applied to specific queries embedded within database objects (stored procedures, functions, triggers) or specific, stand-alone SQL statements.
A plan guide is created using the sp_create_plan_guide system-stored procedure: sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N' { OBJECT | SQL | TEMPLATE }' , [ @module_or_batch = ] { N'[ schema_name.]object_name' | N'batch_text' | NULL }
, [ @params = ] { N'@parameter_name data_type [,...n ]' | NULL } , [ @hints = ] { N'OPTION ( query_hint [,...n ] )' | NULL }
Table 28-7. sp_create_plan_guide Arguments
Argument Description
plan_guide_name The name of the new plan guide.
statement_text The SQL text identified for optimization.OBJECT | SQL | TEMPLATE When OBJECT is selected, the plan guide will apply to the statement text found within a specific stored procedure, function, or DML trigger. When SQL is selected, the plan guide will apply to statement text found in a stand-alone statement or batch. The TEMPLATE option is used to either enable or disable parameterization for a SQL statement. Recall from Chapter 22, in the topic “Modify Database Parameterization Behavior” that the PARAMETERIZATION option, when set to FORCED, increases the chance that a query will become param- eterized, allowing it to form a reusable query execution plan. SIMPLEparameterization, however, affects a smaller amount of queries (at SQL Server’s discretion). The TEMPLATE option is used to override either a database’s SIMPLE or FORCED parame- terization option. If a database is using SIMPLE parameterization, you can force a specific query statement to be parameterized. If a database is using FORCED parameterization, you can force a specific query statement to not be parameterized.
N'[schema_name.]object_name' | Specifies either the name of the object the SQL text will be in, N'batch_text' | NULL the batch text, or NULL, when TEMPLATE is selected.
N'@parameter_name data_type The name of the parameters to be used for either SQL or
[,...n ]' | NULL TEMPLATEtype plan guides.
N'OPTION ( query_hint The query hint or hints to be applied to the statement. [,...n ] )' | NULL
To remove or disable a plan guide, use the sp_control_plan_guide system-stored procedure: sp_control_plan_guide [ @operation = ] N'[ DROP | DROP ALL | DISABLE | DISABLE ALL | ENABLE | ENABLE ALL ]', [ @name = ] N'plan_guide_name' ]
The arguments of this command are described in Table 28-8. Table 28-8. sp_control_plan_guide Arguments
Argument Description
DROP The DROP operation removes the plan guide from the database. DROP ALL DROP ALLdrops all plan guides from the database.
DISABLE DISABLEdisables the plan guide, but doesn’t remove it from the database.
DISABLE ALL DISABLE ALLdisables all plan guides in the database.
ENABLE | ENABLE ALL ENABLEenables a disabled plan guide, and ENABLE ALL does so for all disabled plan guides in the database.
Figure 28-26. Query execution plan prior to applying a Plan Guide
In this recipe’s example, a plan guide is used to change the table join type method for a stand- alone query. In this scenario, your third-party software package is sending a query which is causing a LOOP join. In this scenario, you want the query to use a MERGE join instead.
■Caution
SQL Server should usually be left to make its own decisions regarding how a query is processed. Only under special circumstances, and administered by an experienced SQL Server professional, should plan guides be created in your SQL Server environment.In this example, the following query is executed using sp_executesql: EXEC sp_executeSQL N'SELECT v.AccountNumber, p.PostalCode
FROM Purchasing.Vendor v INNER JOIN Purchasing.VendorAddress a ON v.VendorID = a.VendorID
INNER JOIN Person.Address p ON a.AddressID = p.AddressID'
Looking at this query’s execution plan in Figure 28-26 shows that the Vendor and VendorAddress table are joined together using a Nested Loop operator.
If, for example, you wanted SQL Server to use a different join method, but without having to change the actual query sent by the application, you can apply this change by creating a plan guide.
The following plan guide is created to apply a join hint onto the query being sent from the application:
EXEC sp_create_plan_guide
@name = N'Vendor_Query_Loop_to_Merge',
@stmt = N'SELECT v.AccountNumber, p.PostalCode FROM Purchasing.Vendor v INNER JOIN Purchasing.VendorAddress a ON v.VendorID = a.VendorID INNER JOIN Person.Address p ON a.AddressID = p.AddressID',
@type = N'SQL',
@module_or_batch = NULL, @params = NULL,
@hints = N'OPTION (MERGE JOIN)'
After creating the plan guide, the query is executed using sp_executesql: EXEC sp_executeSQL N'SELECT v.AccountNumber, p.PostalCode
Figure 28-27. Query execution plan after applying a Plan Guide
ON v.VendorID = a.VendorID
INNER JOIN Person.Address p ON a.AddressID = p.AddressID'
Looking at the graphical execution plan in Figure 28-27, you’ll now see that the Nested Loop joins have changed into Merge join operators instead—all without changing the actual query being sent from the application to SQL Server.
If it is decided that this Merge join is no longer more effective than a Nested loop join, you can drop the plan guide using the sp_control_plan_guide system-stored procedure:
EXEC sp_control_plan_guide N'DROP', N'Vendor_Query_Loop_to_Merge'
How It Works
Plan guides allow you to add query hints to a query being sent from an application without having to change the application itself. In this example, a particular SQL Statement was performing Nested Loop joins. Without changing the actual query itself, SQL Server “sees” the plan guide and matches the incoming query to the query in the plan guide. When matched, the hints in the plan guide are applied to the incoming query.
The sp_create_plan_guide allows you to create plans for stand-alone SQL statements, SQL statements within objects (procedures, functions, DML triggers), and for SQL statements that are either being parameterized or not, due to the database’s PARAMETERIZATION setting.
In this recipe, the first parameter sent to sp_create_plan_guide was the name of the new plan guide:
EXEC sp_create_plan_guide
@name = N'Vendor_Query_Loop_to_Merge',
The second parameter was the SQL statement to apply the plan guide to (white space characters, comments, and semicolons will be ignored):
@stmt = N'SELECT v.AccountNumber, p.PostalCode FROM Purchasing.Vendor v INNER JOIN Purchasing.VendorAddress a ON v.VendorID = a.VendorID INNER JOIN Person.Address p ON a.AddressID = p.AddressID',
The third parameter was the type of plan guide, which in this case was stand-alone SQL: @type = N'SQL',
For the fourth parameter, since it was not for a stored procedure, function, or trigger, the @module_or_batchparameter was NULL:
@module_or_batch = NULL,
The @params parameter was also sent NULL since this was not a TEMPLATE plan guide: @params = NULL,
The last parameter contained the actual hint to apply to the incoming query—in this case forcing all joins in the query to use a MERGE operation:
@hints = N'OPTION (MERGE JOIN)'
Finally, the sp_control_plan_guide system-stored procedure was used to drop the plan guide from the database, designating the operation of DROP in the first parameter, and the plan guide name in the second parameter.