IT Bytes

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.