Another reason for a different query plan

While investigating an issue with a poor performing query a colleague realised that although it ran quickly in SQL Workbench it was causing timeouts  from .net clients. He used a nice trick of running the same query using OSQL (ADO client) to run the query and simply wait until the query ran. Why does this work? Well I believe the problem is that the query is too complicated for the plan to be created before the command to execute the query has timed out. But why doesn’t boot-strapping the plan in SQL Workbench help? I’ve struggled to answer this before but I may (may) have found the answer in SET OPTIONS. Query plans are cached for each group of set options, therefore there is a fair chance that the default ADO set options differ from those used by SQL Workbench. Although that in itself is interesting what it still doesn’t explain is why one set of options produces plans faster than another. It sounds a little odd to me, but it’s worth investigating further.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

One Response to Another reason for a different query plan

  1. Paulio says:

    Further investigation for this specific issue shows that after a maintenance job has run the plan becomes some-how corrupted. The only workaround so far is run DBCC FreeProcCache, a bit excessive but it\’s the only thing that works! 

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