Tag Info

Hot answers tagged

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

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


8

This table is very small! It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints). CREATE TABLE Person.ContactType( ContactTypeID int IDENTITY(1,1) NOT NULL, Name dbo.Name NOT NULL, ModifiedDate datetime NOT NULL, CONSTRAINT ...


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

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.


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


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

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

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

Both the Plan Cache and 'optimize for adhoc workloads' are per instance.


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

I can't say for certain because I've never dealt with that large a stored procedure but you could try this: SELECT query_plan FROM sys.dm_exec_procedure_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE object_id = object_id('procedure_name') Then save the output to a text file.


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


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

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

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


1

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes. Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index. The missing index feature isn't always ...


1

How is it possible different databases are using different query plans? It all depends on the options, statistics, queries, indexes, etc that are setup. I couldn't begin to explain to you. What I would recommend you do is if you know the plan it should use, you can give query hints (usually a bad idea) or you can rewrite your query. First of all, you do ...


1

Multiplying the rows is invalid for several reasons: Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but ...


1

The two queries are functionally identical so should perform the same and as you can see from the query plans SQL server has indeed spotted that there is no difference between the two. Of course as soon as you need to perform and aggregate operations you need to use grouping instead, otherwise you'll end up having to use the "distinct" version as a derived ...


1

There are other parameters that can influence execution plan, like PGA size, number of CPUs, disk I/O speed, different sample sizes when you are gathering statistics, index modifications, different system load, different block sizes and so on and on. Just too many to check them all. You could try sqlTXplain or sql Health Check Oracle's tools to find the ...



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