X hits on this document

119 views

0 shares

0 downloads

0 comments

45 / 48

CHAPTER 28 PERFORMANCE TUNING

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.

Figure 28-26. Query execution plan prior to applying a Plan Guide

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 FROM Purchasing.Vendor v INNER JOIN Purchasing.VendorAddress a

679

Document info
Document views119
Page views119
Page last viewedFri Dec 09 14:25:32 UTC 2016
Pages48
Paragraphs987
Words15822

Comments