The strategy selected by the query optimiser to process a query.

learn more… | top users | synonyms

6
votes
2answers
346 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
2
votes
1answer
52 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same ...
12
votes
2answers
119 views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
1
vote
1answer
118 views

Estimated rows very wrong when using declared variables

I am getting very, very wrong estimates for the clustered index seek step of this query (estimated rows is 8,637,530; actual is 74,723): Declare @StartDate as date = '10/1/2014'; Declare @EndDate as ...
3
votes
2answers
105 views

Changing metadata changes query output issue [duplicate]

Recently stumbled across the following problem: changing metadata changes query output. Here is how to get it. CREATE TABLE [dbo].[prod] ( [ID] [tinyint] NOT NULL, [values] [tinyint] NOT NULL ) GO ...
1
vote
1answer
39 views

SQL Server 2012 Execution Plan

I was reading through Kendra Little's post on www.brentozar.com http://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/ In the post, Kendra mentioned that she ...
0
votes
1answer
28 views

What are the OMem and 1Mem columns in the plan output?

The plan output, for example SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( '' ,NULL,'ALLSTATS LAST')); has the columns OMem and 1Mem. What is their meaning? I could not find any reference or ...
2
votes
2answers
129 views

SQL Server doesn't use expected index

I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL): SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP ...
1
vote
1answer
62 views

Query Plan showing missing index warning on trivial statements

I have a Stored Procedure (ReportAdvancedUP) which runs fast (~1sec) in MSSMS but takes a long time (~1.8mins) via our Web Application. I am aware that this usually comes down to bad choice of ...
0
votes
1answer
87 views

How many LEFT JOINS make a VIEW unusable?

I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected. For example, with the ...
5
votes
3answers
364 views

Parallel Plan Selection

I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
1
vote
1answer
50 views

Deciding which MySQL execution plan is better

Background: I have a query that I'm trying to speed up, involving 3 tables: omgenvelope has 5 million rows omgcust has 195 rows omginput has 35836 rows I'm obtaining the distinct omginputs ...
1
vote
1answer
77 views

Reading a SQL Server Execution plan

I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my ...
3
votes
1answer
133 views

Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] ...
6
votes
1answer
246 views

Optimize for ad-hoc workload

I know what this option does and how to enable it. My question is what things will happen if I were to enable this. Without giving too much info, our accounting system is a Microsoft Dynamics ...
0
votes
0answers
64 views

Index Tuning Question

I have the following query: SELECT T1.ITEMID, T1.ITEMTYPE, T1.PURCHMODEL, T1.HEIGHT, T1.WIDTH, T1.SALESMODEL, T1.COSTGROUPID, T1.REQGROUPID, T1.EPCMANAGER, ...
1
vote
1answer
96 views

Execution Plan in Microsoft SQL Server

I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine. Question 1: If I execute following queries from Hibernate, will database create a separate execution plan ...
1
vote
1answer
88 views

Query Plan Different When Using Compiled Values

I have pulled out an execution plan from the plan cache and from the XML pulled out the compiled values. Then I have run the query in SSMS using the compiled values and the query plans are different ...
5
votes
2answers
385 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
0
votes
0answers
61 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
1
vote
0answers
84 views

Different Execution Plans Picked for Similar Queries

Thank you in advance for reading it. This question is kind of long and need a little patience to read. I will provide as much details as I can to void interference factors to solve this problem. ...
1
vote
1answer
88 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
2
votes
1answer
58 views

Understanding the simple execution plan in PostgreSQL

I'm new to execution plans still trying to learn. My problem is for below query select * from item limit 3; I got following execution plan, "Limit (cost=0.00..0.07 rows=3 width=75)" ...
1
vote
1answer
170 views

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the ...
2
votes
1answer
653 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
2
votes
1answer
130 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of ...
0
votes
0answers
25 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?
7
votes
1answer
363 views

Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”

I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old. Through running the following query (courtesy of ...
11
votes
1answer
297 views

How deterministic is execution plan creation in SQL Server?

Given the following constants: The same database with the same structure (tables, indexes, etc) The same data The same SQL Server and hardware configuration The same statistics The same SET options ...
4
votes
2answers
567 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
0
votes
1answer
116 views

Oracle 10g: same query, different plans on copies of the same DB

I have a query joining a number of long tables (primary key in one table, indexed column in another) which results in different plans on different copies of the same database. The structure of copies ...
3
votes
1answer
83 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
0
votes
1answer
50 views

Access and filter in oracle plan

I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them. My question is what are the reasons that can cause that(all ...
0
votes
1answer
63 views

Comparing execution plans [closed]

How does the optimizer compare different execution plans of the same query? Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same ...
0
votes
0answers
138 views

Explain difference between insert as select and select

Can someone explain why there is plan difference? Same query, same time the explain plan taken. I understand what happen (different order of getting the data) and how to fix it(move to forall insert ...
1
vote
1answer
126 views

Understanding MySQL's 'explain'

I have following query with explain: mysql> explain select dd.data from dane dd join test1.tag1 t1 on dd.id = t1.id join test1.tag2 t2 on dd.id = t2.id join ...
8
votes
1answer
312 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
2
votes
1answer
152 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
3
votes
2answers
214 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
2
votes
0answers
93 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
5
votes
1answer
192 views

Capture Complex Actual Execution Plans

I have a stored procedure that among other things builds and executes a large number of Dynamic SQL statements. (Don't hate me, I didn't write it.) I would like to retrieve the actual execution plan ...
3
votes
1answer
671 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
2
votes
0answers
48 views

Identical query & instance but other execution plans & performance [duplicate]

I have 2 identical queries . One is executed with parameters, the other is executed with hardcoded values. Somehow sql server is creating 2 different execution plans for the 2 queries. The first one ...
4
votes
1answer
192 views

Why clustered index scan?

I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup. SELECT ct.* FROM Person.ContactType AS ct ...
2
votes
1answer
94 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
1
vote
0answers
77 views

What to look for in query execution plans? [closed]

Assuming the query execution is slow, I have collected the xml plan. What should I look for in the actual execution plan. What are the top 10 things I need to watch out for? I know this is a broad ...
3
votes
1answer
94 views

Simple SELECT takes forever, but works as a subquery. Why?

I'm running a MySQL 5.1 server and I have a MyISAM table called my_table with nearly 5 million records. Its structure is the following: ...
8
votes
1answer
280 views

Does SQL Sentry Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the ...
0
votes
1answer
2k views

Different execution plan for the same query if I change a value in the predicate

I am getting two different execution plans for the same query only by changing the value in the predicate, the first one uses nested loops and the other one hash join which takes a lot longer. ...
0
votes
1answer
131 views

SQL Server Index Scan Actual Executions

I have a fairly complicated query and running it on two databases with same schemas, but with different data produces different result. On database D1 the elapsed time is around 1 second, and the ...