SQL server

Query optimization using plan guides.

Third party applications that access the DB use SQL code that cannot be modified by the DBA. However, this SQL code will have to be run off the SQL server, and there is a way to optimize this code using plan guides. Query hints/plans are suggested to the SQL code (generated by the application), and may improve the performance of the application generated SQL code. The end result is to return the same data in a more efficient manner by tweaking the query. Plan guides are created using the sp_create_plan_guide procedure. The ‘hints’ specified are used to replace the original SQL statement in the hope of improving performance. Query plan guides can be used on SQL objects (SPs), SQL statements, and SQL templates.

I find this option very useful. Many a times, SQL written by our in house developers are not optimized. Plan guides provide me a way to test and prove that the SQL server is not slow due to a DBA deficiency.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s