Tag Info

Hot answers tagged

11

This is a bug in SQL Server (from 2008 to 2014 inclusive). The filtering condition is pushed down into the scan operator as a residual predicate, but the memory granted for the sort is erroneously calculated based on the pre-filter cardinality estimate. To illustrate the issue, we can use (undocumented and unsupported) trace flag 9130 to prevent the Filter ...


11

Yes. You need the USE PLAN hint. In which you supply the XML from the first plan. SELECT * FROM T OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....') Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.


11

Given these constants, will SQL Server always produce the same plan for a given query? If not, are there other considerations? Is there also an element of nondeterminism to consider as well? Query compilation is deterministic as far as I am aware. One of the original QO design goals was that it should be possible to reproduce execution plans on a ...


9

I don't think this query is about TVPs so much as complexity. The plan has an optimizer timeout. You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml. These are not always a disaster, it just means the optimizer has run out of time ...


9

The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below. For the other two nonclustered ...


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


7

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel. In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning ...


7

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior? The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of ...


6

Kris, •Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above? That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much ...


6

The query must match EXACTLY, including whitespace. I suggest you get the query from the cache, and create it from that rather than entering it any other way.


5

Most likely, either: The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or It was an ad-hoc batch and the text of your query did not exactly ...


5

According to this, the second run of an ad hoc batch removes the stub (which was used only once) and creates and caches the plan (using it for the first time). I also haven't seen many references to refcounts other than it being a count of references by cache objects. Adhoc Compiled Plan objects can still have a refcount of 1, so it's not exclusively caused ...


4

type: index means it's an index scan. That is, it's scanning through an entire index of that table. An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index. There are as many ...


4

No, there is no way to save a query plan to disk, force a query plan, etc. Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here: http://wiki.postgresql.org/wiki/Slow_Query_Questions Check your rowcount estimates ...


4

Is this behaviour correct? It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion. The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans. As a consequence, ...


3

Interesting. It seems this is due to the addition of the unique constraint, which adds an index on ID, which in turn causes the optimizer to use an index seek rather than a table scan. Unfortunately I have no idea why the index seek doesn't seem to throw a divide by zero error, as it shows the division in the seek predicate. DECLARE @X TABLE ( ID Int ...


3

I believe Kendra is using a front end application called SQL Sentry Plan Explorer. It's a really great tool and gives a little bit more verbose info about the execution plan. Here's an example of a query I'm working on right now with Plan Explorer: It's also free-ninety-nine! (the paid version gives you some more bells and whistles but the free version ...


3

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything. Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached. When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc ...


3

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment ...


3

With a table that large I would consider partitioning. Unfortunately I can't answer your specific questions (1-3) but in general one of the benefits of using partitioned views (instead of native partitioning) is that the individual tables within the partitioned view are considered separate objects and have their own statistics each with 200 steps. Here is ...


3

The link provided by Martin Smith to an existing SO question provided me with the answer: The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


3

You're on the right track - the XML index is the problem. Obviously, there's a primary as well as a secondary XML index. When performing a DELETE against the base table (ETLHeaders) the data have to be deleted from every index of this table, too. This overhead can be significant, especially for XML indexes. The index causing the long duration is the ...


3

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? No, what happens is that the execution plan for a stored procedure is cached. Assuming there is enough available memory to continue holding the plan, it won't change unless one of the ...


3

Am I correct in saying that statistics are only used when creating the execution plan No, out-of-date statistics may cause an optimality-related recompilation of the affected statement. We have an ascending date/time column in one of our largest tables that we query regularly Sub-optimal execution plans caused by predicate values being outside ...


3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled). So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or ...


2

Try: LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId AND LastOperationTime IS NOT NULL The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs ...


2

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ? That's not (necessarily) what's happening. Outer nodes consume rows from inner nodes one by one with a demand-pull model. So in this ...


2

From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case. In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the ...


2

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions. Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning. ...


1

In the version of the query with FORCESEEK, the majority of the query cost is in the key lookup. You can eliminate this key lookup by creating a nonclustered index on GridRunId and MancoId. Have a look at the estimated cost figure in the SELECT (root) node of your plans. The optimizer will chose the plan with the lowest estimated cost. By adding a covering ...



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