Tag Info

Hot answers tagged

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


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.


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

So is there a way I can see the execution plan of the proc or the statement in particular? You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into. Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with ...


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

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

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


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

(Moved my comment to an answer, and expanded a bit.) I cannot create indexed views as I've some calculations as well like SUM(Volume * CaseCount) AS SomeColumn which is not allowed in indexed views and also it's a dynamic query and grouping and joins are based on user input. From the plan you provided, the clustered index scan is 3.5x overestimated, ...


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


2

Some of the statistics statements in the setup script fail with an error. Some people will give up providing an answer if the setup script errors out. Perhaps it would be better to script statistics using the built-in features of SSMS rather than writing your own script? The join confuses me because it makes the update non-deterministic. If you'd used MERGE ...


2

@Kin has a very good answer so I do not intend to supplant his answer, but to supplement. You can also get the plan from Profiler itself. This would be especially helpful if the plan had been evicted from the Plan Cache before you had a chance to analyze it. In the "New Trace" panel click on the "Events Selection" tab. Then check the "Show all events" and ...


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

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

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

After researching for a while, the problem was that a parameter was different between the two databases. Database A had _complex_view_merging set to FALSE and Database B didn't have it. I just changed _complex_view_merging to TRUE in Database A and the query executed as quickly as Database B.


1

you can always use Server Sid trace for lower impact on user side performance but in your case its nightly job so you can start Profiler and in the event choose showplanXML. you can also choose to extract the XML plan for all queries in single file or in separate file. as you also know what procedure you need to trace, filter the trace events by that ...



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