Developing Matt

My Technical Journal

Execution Plan TEXT

leave a comment »

It’s important to fine-tune your sql queries as much as possible.  I’m still learning a quite a bit to this. A couple of quick and easy ways to check out your query is to

  1. show the execution plan.  There is alot of information, but if you want a quick and easy way to optimize your query then write two queries and show the execution plan.  Then modify the second query and run it again to see which query runs faster. 
  2. Another way is to show the text version of the execution plan with SET SHOWPLAN_TEXT ON.    This will give you a text rundown of the scans, seeks, indexes used, etc.   Don’t forget to SET SHOWPLAN_TEXT OFF.  (also, If you are using temporary tables in the query use 
    SET STATISTICS PROFILE ON )  The following came from here
  • Index or table scans: May indicate a need for better or additional indexes.
  • Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
  • Filter: Remove any functions in the WHERE clause, don’t include wiews in your Transact-SQL code, may need additional indexes.
  • Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?
Advertisements

Written by matt

February 6, 2008 at 1:19 pm

Posted in Sql Performance

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: