New answers tagged

0

I would say that there are probably more query statements in the batch that produced that warning than you are showing here (or potentially even a DLL Trigger). The [sys].[sysschobjs] table is an internal, system catalog table that cannot be referenced directly (unless you connect to the DAC -- Dedicated Administrator Connection). However, that table is ...


3

If you right-click on the warning -> properties you should see some Warnings:


4

These are query planner warnings. In the actual execution plan (perhaps not the estimated one) I would expect to see a "warnings" section listing what the planner/engine is concerned about. A common warnings are when an operation spills data to disk or was wanting index statistics that were unavailable. The most common warning is for apparently missing ...


0

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


3

It's not an operator in its own right. It is an output column from the table scan operator on the heap. It is the "bookmark" that contains the physical address of the row (this is the same bookmark as is referred to in the phrase "bookmark lookup"). This is passed along the pipeline into the update operator so it knows the row it should be updating. In SQL ...


2

why it expects for 1 row (in Clustered index seek), when I specify recompile That is fine. Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator. A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all). In your case you have 2,000 seeks all returning 1 row ...


3

Assuming your variables are always NOT NULL WHERE ( ReceiveDateTime < @DocOldestTreshold OR ( ReceiveDateTime < @DocNewestTreshold AND ReceiveDateTime < t.Treshold ) ) Is equivalent to WHERE ReceiveDateTime < @DocOldestTreshold OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold) Which is ...


7

Because the majority of the table fits the criteria for the first query, so it is more efficient to scan the clustered index rather than do key lookups for each of the rows that match the criteria. Key lookups are expensive, and so are usually only used when a small percentage of the table fits the WHERE criteria. Once the query returns a certain ...


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


0

Grant, I believe a coworker (Dennis Rogers) and I have answered the question. Here appears the definitive formula that SSMS uses to calc the operator cost and the cost %. Estimated Operator Cost == @EstimatedTotalSubtreeCost - Sum(Immediate Children@EstimatedTotalSubtreeCost) Estimated Operator Cost Percent = Estimated Operator Cost / StmtSimple@...


2

The only way to properly answer that question is to fire up the debugger and see what choices were made by the optimizer along the way. The costs are not only IO and CPU. There are additional costs associated with a given operator that are reflected in the total cost, but are not reflected in the IO and CPU cost estimates. You can read more about some of the ...


1

Create a function like the one below; CREATE OR REPLACE FUNCTION public.explain_this ( l_query text, out explain text ) RETURNS SETOF text AS $body$ BEGIN RETURN QUERY EXECUTE 'explain ' || l_query; END; $body$ LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY DEFINER COST 100 ...


0

EXPLAIN estimates row counts, often poorly. Even so, the counts do not reflect how much stuff is hauled around. If it is only id, that is not much. If it is all of * (as in SELECT *), that is a lot more. This is especially the case when it has to reach into the data to get all the columns. Using index means that it is cruising through the index BTree. ...


11

I suspect the difference is the implicit conversion, which can get in the way. As an example, Bar isn't touched if it has a compatible data type: CREATE TABLE [dbo].[Bar] ( [Value] int NULL, [Value2] int NULL );


1

Be aware - I posted this before index definitions were posted Insert in the order of the PK to keep fragmentation down INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT 2, value, 0 FROM Z_SIMULATION_0 WHERE ID >= 1 AND ID <= 500000 ORDER BY value; or INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT top (...


4

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on. When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or ...



Top 50 recent answers are included