New answers tagged

4

Your execution plan for the "individual queries" shows that pre-calculating the StartID, Category etc allows an index to be used efficiently on A, "seeking" straight to the record(s) you want (you have a Non-Clustered Index Seek in your query plan), having identified the given Category etc to search for. The "single-query" with JOINs, on the other hand, ...


7

Execution plan still shows ClusterIndex Seek why? The initial seek down the b-tree is to find the first row where CustomerID >= 1. From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon ...


7

The thing to remember here is that execution plans suck the data through. So the Nested Loop operator calls the Stream Aggregate 4 times. The Stream Aggregate calls the Filter 4 times as well, but only gets a value twice. So the Stream Aggregate gives four values. Twice it gives a value, and twice it gives Null.


9

The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty. For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances. -- NULL for a ...


2

The answer would be simple here, assuming you just have one clustered index on table and your query is like select * from customer where CustomerID between 1 and 70000 In above case seek would be preferred by optimizer because first the index would search the data based on condition CustomerID=1 and would find the first row which matches the predicate. ...


0

The most effective method I've found is to give the query plan more information about the expected values of parameters. Option (optimise for (@var1 = 2000, @var2 = 4000)) Apologies writing this from mobile so not as easy to test against your query


1

You could create a stored procedure that uses dynamic SQL to allow SQL Server to decide the best plan for a given value of col3. This is similar to @Martin's answer with the OPTION (RECOMPILE), with the added benefit of execution plan caching for queries that run more than once. CREATE TABLE dbo.t1 ( col1 varchar(255) NOT NULL, col2 varchar(255) ...


8

The values of variables aren't generally sniffed so it will just assume a flat 30% of the table will be returned for that greater than predicate against an unknown value (cf. Selectivity Guesses in absence of Statistics). When you use the literal it can look up the known value in the column statistics to get a much more accurate estimate. If it estimates ...


0

Whenever the optimizer looks at a query to produce a plan the relevant statistics are examined and it will compare the cost of various plans and pick the cheapest. It doesn't look at all possible plans as it can 'shortcut' some options. An execution plan will be produced whenever a hard parse occurs and this only happens when the query to be executed does ...


10

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


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


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


-5

CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO


0

I'd been also curious about why sometimes some cost is displayed as 100%, 200%, 300%...and even more. After analyse xml file of query plan, i got it. The cost percentage = my EstimatedTotalSubtreeCost / parent node's EstimatedTotalSubtreeCost For example, your query plan shows Clustered Index Insert take cost 914%, to understand how it calculate this ...


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


-4

Subqueries are always low on performance when involved with joins. Try replacing the subquery with a LEFT JOIN TB_BRANCH b ON t1.BRANCH_ID = b.BRANCH_ID WHERE b.START_DT <> '99999999' and see if the performance improves.


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


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


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


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



Top 50 recent answers are included