Lesson 2: Improving Query Performance

Lesson 2: Improving Query Performance

Estimated lesson time: 45 minutes

One of the main reasons SQL Server has become popular is because of its self-tuning capabilities. Still, there might be situations where experienced database developers and administrators want to alter the execution plan in an attempt to aid a poorly performing query. SQL Server 2005 offers several options for optimizing the performance of your queries. This is known as plan forcing, and it means you will force the query optimizer to use a specific plan. This is not a technique that should be used frequently or carelessly because the query optimizer will normally identify the optimal execution plan.

Showing the Execution Plan

For every query that is executed, you have the option of viewing how the database engine will handle that query. For instance, the execution plan will show whether it needs to execute a table scan or an index scan. To see what this query execution plan looks like, you can select Display Estimated Execution Plan from the Query menu in SQL Server Management Studio. The Execution Plan tab will appear and give you a graphical representation of the execution plan. If you hover the mouse pointer over a node, it will show you the detail associated with that node. (Refer to Figure 2-4.)

Figure 2-4: Execution plan for a query executed in SQL Server Management Studio

Alternatively, you can add the following Transact-SQL code before the query executes:

 SET SHOWPLAN_XML ON GO 

When you add this Transact-SQL statement, it causes all statements that follow to not be executed. This condition will remain until you execute a statement that turns the SET SHOWPLAN option OFF. Instead of executing the query, you will be returned a formatted XML string that contains the query plan information.

Using Query Hints

One method for optimizing queries is the query hint, which can be specified in the OPTION clause of a SELECT, UPDATE, DELETE, or INSERT statement. Although the concept of a query hint is not new to SQL Server 2005, the way it is implemented has improved.

One of the options available with the query hint is USE PLAN. The USE PLAN query hint takes an XML string as an argument. The XML string should be in the same format as the string displayed when you execute SET SHOWPLAN_XML ON. Keep in mind that if you use this method to modify an execution plan, you will need to replace all instances of single quotes with double quotes. The following is an XML string that was returned after executing a query against the AdventureWorks database:

 <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1399.06"> <BatchSequence>   <Batch>     <Statements>       <StmtSimple StatementText="SELECT Name, ProductNumber, ListPrice&#xD;&#xA;FROM Production.Product&#xD;&#xA;WHERE ProductID = 1&#xD;&#xA;" Statement StatementComp StatementType="SELECT" StatementSubTreeCost="0.0032831" StatementEstRows="1" StatementOptmLevel="TRIVIAL">         <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />         <QueryPlan CachedPlanSize="9">          <RelOp Node PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="96" EstimatedTotalSubtreeCost="0.0032831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">             <OutputList>               <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="Name" />               <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="ProductNumber" />               <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="ListPrice" />             </OutputList>             <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">               <DefinedValues>                 <DefinedValue>                   <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="Name" />                 </DefinedValue>                 <DefinedValue>                   <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="ProductNumber" />                 </DefinedValue>                 <DefinedValue>                   <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="ListPrice" />                 </DefinedValue>               </DefinedValues>               <Object Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Index="[PK_Product_ProductID]" />               <SeekPredicates>                 <SeekPredicate>                   <Prefix ScanType="EQ">                     <RangeColumns>                       <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Column="ProductID" />                     </RangeColumns>                     <RangeExpressions>                       <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@1],0)">                         <Convert DataType="int" Style="0" Implicit="1">                           <ScalarOperator>                             <Identifier>                               <ColumnReference Column="@1" />                             </Identifier>                           </ScalarOperator>                         </Convert>                       </ScalarOperator>                     </RangeExpressions>                   </Prefix>                 </SeekPredicate>               </SeekPredicates>             </IndexScan>           </RelOp>           <ParameterList>             <ColumnReference Column="@1" ParameterCompiledValue="(1)" />           </ParameterList>         </QueryPlan>       </StmtSimple>     </Statements>   </Batch> </BatchSequence> </ShowPlanXML> 

Notice that the RelOp node displays the physical and logical operations that will take place. For this query, a clustered index seek is utilized. For more information about what nodes can be altered, refer to the MSDN documentation titled "Using the USE PLAN Query Hint" at http://msdn2.microsoft.com/en-us/library/ms186954.aspx.

Best Practices 

Continually evaluate queries with altered execution plans

It is likely that altering the execution plan will cause the query to perform even more slowly than it did previously. It is also possible that the query could perform differently after an upgrade or service pack is applied. Therefore, if you choose to alter the execution plan, be prepared to periodically evaluate the query to ensure that it is still performing adequately.

If you want to modify the current execution plan for a query, you will modify the XML string and then execute a query similar to the following instead (note that the entire XML string was not included and that ellipses represent the collapsed portion):

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE ProductID = 1 (OPTION USE PLAN N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/ showplan" Version="1.0" Build="9.00.1399.06">   <BatchSequence>     <Batch>       <Statements>       </Statements>     </Batch>   </BatchSequence> </ShowPlanXML>') 

Using Plan Guides

Plan guides are a new feature available with SQL Server 2005. This feature enables you to inject query hints into SQL statements without modifying the query itself. This can be useful when troubleshooting problems with third-party applications, and you do not have access to the code. Instead of modifying the query like we did with the USE PLAN query hint, plan guides use an internal system table to track whether plans are attached to certain queries.

You create a plan guide using the sp_create_plan_guide system stored procedure. This built-in stored procedure accepts the following parameters:

• @name Plan guide name.

• @stmt Actual SQL statement. You can use SQL Server Profiler to extract the text for the SQL: BatchStarting event. (See Figure 2-5.) You must replace all single quotes with double quotes.

• @type Value this parameter as OBJECT if you are referring to a stored procedure, function, or trigger. Use the value SQL if it is a standalone SQL statement. Use the value TEMPLATE if it applies to any query that uses the same format as the SQL statement in the @stmt parameter.

• @module_or_batch If you set the @type parameter with a value of OBJECT, then this should contain the schemaname.objectname of that object. If the @type parameter was set with a value of SQL, then this should contain the same statement text specified in @stmt. If the @type parameter was set with a value of TEMPLATE, then this must be set with a NULL.

• @params Used to specify all parameters and their data types.

• @hints Used to specify the query hint that applies to this query. You can use the OPTION clause, which is the same clause used in the USE PLAN query hint.

Figure 2-5: Execution plan for a poorly performing query executed in SQL Server Management Studio

Using Searchable Arguments

It is always best for queries to use a WHERE clause because this will restrict the number of results returned and thus enable the query to perform faster. Searchable arguments, also known as SARGs, refer to a WHERE clause that compares a column to a constant value or a variable. This enables the query optimizer to take advantage of an index, which is better than executing a table scan. A table scan should be avoided whenever possible because it indicates that no indexes will be utilized and the database engine will need to scan the entire table.

If you encounter a query that is performing poorly, take a look at whether it is using a search argument properly. If it is not, then determine whether the query can be rewritten. You can do a quick check to see what type of operation will be performed by selecting Display Estimated Execution Plan from the Query menu.

The query optimizer will attempt to resolve a query so that it utilizes a search argument whenever possible. For example, if you were to display the execution plan for the following query, you would see that it utilizes a clustered index seek:

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE NOT Color IN ('Black', 'Silver', 'Red') 

Other developers might tell you that this type of operation should be avoided because the NOT keyword is not a valid search argument (SARG). However, the predicate column for the execution plan indicates that the query optimizer knows the WHERE clause should be interpreted as the following:

 [AdventureWorks].[Production].[Product].[Color] <> N'Black' AND [AdventureWorks].[Production].[Product].[Color] <> N'Red' AND [AdventureWorks].[Production].[Product].[Color] <> N'Silver' 

Despite this, there are still some operations that can prevent the query optimizer from selecting the optimal execution plan. For example, you should not include a function on a searchable column because this can cause the query optimizer not to utilize an index properly. The following query would still utilize an index, but it would perform an index scan:

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE SUBSTRING(name, 1,1) = 'b' 

An index scan means that the database engine will need to scan the whole index page. A faster alternative would be to perform an index seek. The index seek will utilize a sort order, so it can access the index faster. The following query will return the same results but will utilize an index seek instead:

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE name LIKE 'b%' 

In some cases, you might need to join to another table in order to return the correct results. Typically, using a table join or subquery will accomplish this task. Both of the following queries return the same results, display a similar execution plan, and will have a similar resource cost:

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE ProductSubCategoryID =   (SELECT ProductSubCategoryID     FROM Production.ProductSubCategory     WHERE Name = 'Mountain Bikes') SELECT p.Name, p.ProductNumber, p.ListPrice FROM Production.Product p JOIN Production.ProductSubCategory ps   ON p.ProductSubCategoryID = ps.ProductSubCategoryID WHERE ps.Name = 'Mountain Bikes' 

Depending on the needs of your application, either query might be the only way you can accomplish your goal. However, the most efficient way to return the same results is with the following query:

 SELECT Name, ProductNumber, ListPrice FROM Production.Product WHERE ProductSubCategoryID = 1 

This version of the query only needs to perform one index scan to return the results. Of course, you might not be able to execute such a query if the ProductSubCategoryID is not known in advance. There are typically multiple methods to execute a query. If you determine that a query is performing poorly, consider alternative methods and display the execution plan to verify the results.

Lab: Tuning Queries

In this lab, you will experiment with tuning queries in order to improve query performance. The first exercise will involve rewriting a query to speed the execution time for the query. The second exercise will involve the creation of a plan guide to alter the way the query optimizer handles the query without altering the query itself.

The completed lab is available in the \Labs\Chapter 02\Lab2 folder on the companion CD.

Important 

Lab requirements

You will need to have SQL Server 2005 installed before you can complete this lab. Refer to the Introduction for setup instructions.

Exercise 1: Rewrite a Query to Improve Performance

In this exercise, you will examine a poorly performing query by examining the estimated execution plan. You will then examine a replacement for the query and compare the execution plans for both queries.

1. Open SQL Server Management Studio.

2. Connect to the instance of SQL Server 2005 that contains the AdventureWorks database.

3. Select New Query.

4. Add the following code to the query window:

      DECLARE @Name nvarchar(50)   SET @Name = 'll Crankarm'   SELECT Name, ProductNumber,          ListPrice, SafetyStockLevel   FROM Production.Product   WHERE SafetyStockLevel > 500   UNION ALL   SELECT Name, ProductNumber,          ListPrice, SafetyStockLevel   FROM Production.Product   WHERE UPPER(Name) = UPPER(@Name) 

5. Select the AdventureWorks database from the Available Databases drop-down list box, and then click Display Estimated Execution Plan from the Query menu. This will not execute the query but will provide a graphical representation of the execution plan. (See Figure 2-5.) Note that the query execution plan will first perform a clustered index scan to query the Production.Product table. It will also need to perform an index scan and clustered index seek against the same table.

6. The first query was identified as a poorly performing query, and a replacement query has been created. Paste the following code within the same query window (beneath the first query):

      DECLARE @Name nvarchar(50)   SET @Name = 'll Crankarm'   SELECT Name, ProductNumber,          ListPrice, SafetyStockLevel   FROM Production.Product   WHERE SafetyStockLevel > 500 OR         [Name] = @Name 

7. Highlight the new query, select the AdventureWorks database from the Available Databases drop-down list box, and then click Display Estimated Execution Plan from the Query menu. The main difference between this query and the first one is that this query does not perform a UNION ALL. The first query used the UPPER function to ensure that a match was made against the input variable. The UPPER function is not necessary if you are performing a query against a case-insensitive database.

8. The second query can utilize an execution plan that involves a single clustered index scan on the Production Product table. Because only one of these scans is needed, fewer resources are utilized.

Exercise 2: Create a Plan Guide

In this exercise, you will create a plan guide to change the way a query is ordered. The plan guide is used because you are not able to modify the original query. The revised query moves one of the joins and then uses FORCE ORDER to force the query optimizer to use the order specified in the FROM clause. The end result is that the results are ordered differently.

1. Open SQL Server Management Studio.

2. Connect to the instance of SQL Server 2005 that contains the AdventureWorks database.

3. Select New Query.

4. Add the following code to the query window:

    -- Revised Query SET STATISTICS XML ON; GO EXEC sp_executesql @stmt = N'SELECT c.FirstName + '' '' + c.LastName,   c.EmailAddress, a.city, jc.resume FROM Person.Contact c JOIN HumanResources.Employee e   ON c.ContactID = e.ContactID LEFT JOIN HumanResources.JobCandidate jc   ON e.EmployeeID = jc.EmployeeID JOIN HumanResources.EmployeeAddress ea   ON e.EmployeeID = ea.EmployeeID JOIN Person.Address a   ON ea.AddressID = a.AddressID OPTION (FORCE ORDER)'; GO SET STATISTICS XML OFF; GO 

5. After executing the query, you will receive two sets of results. In the second set, there is a column named Microsoft SQL Server 2005 XML Showplan. Click the link that appears here, and copy the results to the clipboard.

6. Return to the original query window, and replace the original query with the following. You will need to replace the section <insert XML showplan for the revised query here> with the results you copied to the clipboard. You will also have to perform a find and replace to replace all instances of single quotes (') with four quotes (" ").

    EXEC sp_create_plan_guide   @name=N'TestPlanGuide',   @stmt=N'SELECT c.FirstName + '' '' + c.LastName,   c.EmailAddress, a.city, jc.resume FROM Person.Contact c JOIN HumanResources.Employee e   ON c.ContactID = e.ContactID JOIN HumanResources.EmployeeAddress ea   ON e.EmployeeID = ea.EmployeeID JOIN Person.Address a   ON ea.AddressID = a.AddressID LEFT JOIN HumanResources.JobCandidate jc   ON e.EmployeeID = jc.EmployeeID',   @type=N'SQL',   @module_or_batch=NULL,   @params=NULL,   @hints=N'OPTION (USE PLAN   N'' <insert Show Plan XML for the revised query here> '')' 

7. Execute the query, and ensure that the command is completed successfully.