Hot answers tagged

9

...why the huge performance hit from joining to sys.databases? And why is it inconsistent? There's nothing special about joining to sys.databases. The optimizer happens to choose a plan that is inefficient for the first query. Specifically, in this area of the plan: ...the optimizer chooses a nested loops join to SYSDMEXECCACHEDPLANS, presumably ...


7

The SOS_SCHEDULER_YIELD accumulation is just like I suggested on #sqlhelp. Each of those equates to 4ms of CPU time for the query, and they always show zero resource wait time, as there is no resource wait involved (thread yields the processor and goes directly to the bottom of the Runnable Queue on the scheduler). So - this query was churning through CPU ...


6

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key. Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this: We have seen that ...


5

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...



Only top voted, non community-wiki answers of a minimum length are eligible