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

learn more… | top users | synonyms

2
votes
1answer
32 views

Does DBCC TRACEON() invalidate cached execution plans?

In Microsoft SQL Server, does DBCC TRACEON() invalidate cached execution plans, same as SP_Configure invalidates all stored procedure plans ?
0
votes
1answer
60 views

Why the optimizer does not use an Index Unique Scan?

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical ...
0
votes
1answer
27 views

intermittent performance issue - query with multiple CTE and trashed execution plan

I'm trying to optimize a SQL query that shows variable performance (less than 2 to more than 15s). Overview This is a ColdFusion website, with a SQL Server 2008 R2 backend The query is part of the ...
3
votes
1answer
202 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
0
votes
1answer
34 views

Optimizing a DMV query

I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote ...
2
votes
0answers
84 views

This Extended Events session works on Dev but not on Pro. Why? [closed]

I do have the following Extended Event session on my Dev and Pro boxes: CREATE EVENT SESSION [sp_showplan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1) ...
2
votes
1answer
84 views

Same query sometimes uses a Table Spool

I'm checking out a query which exhibits strangely inconsistent performance. Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't. When it actually used a ...
3
votes
2answers
87 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
10
votes
2answers
184 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
7
votes
2answers
398 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
99 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 ...
13
votes
2answers
177 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
124 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
124 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
45 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
69 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 ...
4
votes
2answers
164 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
75 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
90 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
375 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
78 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
89 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
134 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
555 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
66 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
109 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
92 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
1answer
429 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
69 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
94 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
122 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
61 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
173 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 ...
3
votes
1answer
1k 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
144 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
527 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 ...
12
votes
1answer
315 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
595 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
162 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
92 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
54 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
83 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
187 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
141 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
353 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
184 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
252 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
106 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
230 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 ...