Case Scenario: Creating a Plan Guide

 

You are a developer for a large retail company that uses software from several third-party vendors. All of the third-party applications use SQL Server 2005 as the database, and they typically use stored procedures as part of the data layer. The support contracts established with each of these vendors restricts you from modifying their databases directly. This means you are not allowed to modify any stored procedures that the applications utilize.

In recent months, you have been experiencing performance problems with one of the third-party applications. After extensive analysis using SQL Server Profiler and distributed management views, you have determined that several of the stored procedures contain poorly performing queries. Your manager has asked you if there is anything that can be done to improve performance without violating the third-party vendors support contract. What do you suggest?

Answers

 

In cases where you are not able to modify Transact-SQL directly, you can still improve query performance using a plan guide. The plan guide is used to override the execution plan automatically generated by the query optimizer. As long as the execution plan you come up with returns the same results and performs better than the original query, this is a good way to affect performance without altering the code.