X hits on this document

111 views

0 shares

0 downloads

0 comments

43 / 48

CHAPTER 28 PERFORMANCE TUNING

Notice in the recipe that the first parameter was preceded with the N' Unicode prefix, as sp_executesql requires a Unicode statement string. The first parameter also included the SELECT query itself, including the parameters embedded in the WHERE clause:

EXECUTE sp_executesql N'SELECT TransactionID, ProductID, TransactionType, Quantity FROM Production.TransactionHistoryArchive WHERE ProductID = @ProductID AND

TransactionType = @TransactionType AND Quantity > @Quantity',

The second parameter further defined the data types of each parameter that was embedded in the first parameter’s SQL statement. Each parameter is separated by a comma:

N'@ProductID int, @TransactionType char(1), @Quantity int',

The last parameter assigned each embedded parameter a value, which was put into the query dynamically during execution.

@ProductID =813, @TransactionType = @Quantity = 5

'S',

The query returned eight rows based on the three parameters provided. If the query is executed again, only with different parameter values, it is likely that the original query execution plan will be used by SQL Server (instead of creating a new execution plan).

Applying Hints Without Modifying Application SQL

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 ' [ s c h e m a _ n a m e . ] o b j e c t _ n a m | N'batch_text' | NULL } , [ @params = ] { N'@parameter_name data_type [,...n ]' | NULL } , [ @hints = ] { N'OPTION ( query_hint [,...n ] )' | NULL } ' e

The arguments of this command are described in Table 28-7.

677

Document info
Document views111
Page views111
Page last viewedThu Dec 08 03:19:16 UTC 2016
Pages48
Paragraphs987
Words15822

Comments