Hot answers tagged

10

I want to know how the query execution works here The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example). After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator ...


10

Memory Limit Exceeded The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed. Time Out This reason ...


9

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 ...


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 ...


8

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are: TimeOut MemoryLimitExceeded GoodEnoughPlanFound The articles you mention seem ok for finding these events, are you having a specific problem? The ...


8

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


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

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


3

For the 'good' plan, all the table variable cardinality estimates are 1 row. This is the most common outcome when using table variables, unless trace flag 2453 is enabled, or a statement-level recompilation occurs (for example because OPTION (RECOMPILE) is used, or one of the regular tables in the query has passed its recompilation threshold. For the 'bad' ...


2

We solved the issue. The issue was caused by the new Cardinality Estimator in SQL 2014. We disabled the new estimator by activating trace flag 9481 and removing the query plan from the cache then the query worked again.


2

I found that your query had many redundancies in the conditions, and you used cross joins that were good candidates for simple joins. This might confuse the planner. Perhaps you could try the following rearrangement of the query (it is functionally exactly the same but uses joins and removes all the redundant comparisons) to see what the planner comes up ...


1

You don't need loops, but one CASE per searched value: select ... from ( select ... case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches from tab ) dt where matches > 0 ...


1

One suggestion that I saw when the new CE was announced came from Brent Ozar. You needed to test this out before you went live with 2014. Run on 2014 but with the level indicating 2012 and benchmark the times and plans. Then change to 2014 level and see which plans changed. From there you attempted to tune the bad plans. Trace flag 4199 has been around for ...



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