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


10

Obvious disclaimer: I work for SQL Sentry. The biggest problems we have are: Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. We are subject to the same limitations because we can only ...


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


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


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

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

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


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

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

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

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


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

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


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

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


1

Based on the statistics the oprimizer has estimated this as the cheapest way to get the data. A INDEX FAST FULL SCAN reads the entire index as it is stored on disk using multiblock read. This kind of operation is prefered to other index operation because a high number/fraction of rows with ID1=1110 and ID2=1112 exists in the index and the data is not ...


1

The task of an optimizer is to create several execution plans and compare them (typically by costs). Each execution plan can be seen as logical transformation. For example: a simple join would always be the cartesian product of two tables. Each row from the first is read and combined with each row in the other table. Than the join condition is checked and ...


1

That's a great question. And there are good answers. The engine definitely will use the index even if you don't use every key column. That's especially so if they are in order, as you are talking about. (can anyone else speak to different orders of key columns?) You will benefit just fine from selecting just on the first column alone as a key, or ...


1

As your table is structured now, there are only two "good" options for satisfying your query, neither one ideal, and one of them isn't likely to be very good at all: find all of the articles matching 'article_id' using the (article_id) index, and then sort their timestamps to find the largest one (the one shown in the question)... or iterate through all ...


1

The problem was histograms, I ran statistics and disabled histogram creation and the execution plan used nested loops: BEGIN DBMS_STATS.GATHER_table_STATS (OWNNAME => 'MIDAS', TABNAME => 'MINCISOC', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'); END; If I run it with FOR ALL COLUMNS SIZE AUTO again the same problem because it uses hash join. ...


1

I've got clients with tables that are over 2B rows and the statistics are able to be used to generate good execution plans. Tables with just a few hundred million rows should be no problem. If you really need more statistics on the table you could put filtered statistics on the table, filtered by year or month depending on what's needed so that you can get ...


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



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