Hot answers tagged

18

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Consistency There is no general guarantee of consistency. Estimates may be calculated on different (but logically equivalent) subtrees at different times, using different statistical methods. There is ...


11

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


9

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


8

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.


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


7

The sum of operator costs is more than 100% in execution plan is a known bug and is closed as by design ! Also, AaronBertrand filed a similar bug - SSMS : Execution plan sometimes exceeds 100% If you want to understand how plan costing works .. Paul White explains it at his best here. From the query processor team - What’s this cost? General guidelines ...


5

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


3

I would not worry about the 100% The big number are the big number A lot repeats so start optimizing just one This is just a subset of your query DECLARE @date SMALLDATETIME SELECT Reffd AS NAME , ( SELECT ( ( SELECT count(*) FROM [cal_reg].[dbo].[customer] WHERE upper(Reffd) = upper(main.reffd) ...


2

Estimated sub tree cost is a SUM of cost of all operators preceding the one you are looking at. The easiest example is to look at the left-most icon - it will have an Estimated sub tree cost of whole query plan. There are a lot of sign in a query plan that show it needs optimization, however, I've seen a lot of situations when even perfect plans caused ...


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


1

You can create a mapping table with two columns, one key (a string containing the called procedure name plus arguments if you need that) and one value (an integer containing the number of times the corresponding procedure has been called). The first thing you do in your procedure you want to monitor is to update the mapping table to record the procedure ...


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



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