New answers tagged

0

At first, it looks like you've modified XML, because the query does not exactly match the plan. At second, format your query like this SELECT o2.Column1, o2.Column2, o1.Column3, o1.Column4, o1.Column5 FROM Object1 as o1, Object2 as o2, Object3 as o3 WHERE o1.Column3 = o3.Column6 AND o2.Column1 = o3....


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


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


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


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


0

Look I do it this way in an audit table, each time this function is executed I put it at the foot of the procedure and called each time the query is run. and consultation is : update audit set calls = calls + 1 where func = 'sptest' ; In this way you can already know how many times was called and the date


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


0

During creation of the question process I figured out a way: SELECT IDENTITY(int, 1, 1) AS RowNumber, EventClass, TextData, Duration, SPID, BinaryData, ServerName, DatabaseName INTO trace_table3 FROM fn_trace_gettable('c:\traces\myTraceFile.trc', default); does the trick. The results are kept "together" same way as in the trace table. http://zarez.net/?...


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


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


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.


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


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



Top 50 recent answers are included