Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
869
questions
0
votes
2answers
53 views
Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server
This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
0
votes
0answers
35 views
Clearing plan cache improves performance - bad plans in cache?
I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)
Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
1
vote
1answer
26 views
PostgreSQL incorrectly using primary key index for min/max query
I have a table with the structure similar to this:
CREATE TABLE employees (
id bigserial NOT NULL,
name_id uuid NOT NULL,
department uuid NOT NULL,
details text NULL,
deleted bool ...
0
votes
0answers
56 views
Stored procedure runs slow on SQL Server 2016 compared to SQL Server 2019
The stored procedure is running very slow on SQL Server 2016 as compared to 2019. It's doing some common validation from a temp table and inserting data into primary tables and mapping tables. The ...
1
vote
1answer
25 views
Wrong estimated row count
PostgreSQL 11
Running EXPLAIN returns a row estimate that is way too low compared to the actual row count.
Given the query
SELECT s.offer_id,
category_id, category_name_de, category_name_fr, ...
0
votes
1answer
28 views
SQL extended event captured only the estimated execution plan but no actual execution plan
My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288)
I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 ...
0
votes
1answer
24 views
Bad estimates when sorting, good estimates without sort
I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1....
2
votes
0answers
51 views
Does SQL Server really require fully qualified object references to match a statement to a cached execution plan [duplicate]
From SQL Server Docs, section Execution Plan Caching and Reuse:
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully ...
2
votes
1answer
61 views
How to optimize Merge Anti Join?
While trying to find the fastest way to do a bulk update of a large table, I came up with the following plan:
having main_table (potentially above 1000 mln rows) and update table up to a few million ...
2
votes
1answer
136 views
SQL Server - Prevent Clustered Index Scan on a specific table
My database contains a specific table that is quite large (250+M rows, 100+GB data space).
Generating a clustered index scan on this table is always a bad idea.
This table has multiple indexes and ...
-1
votes
0answers
9 views
Same update, same plan, different reads and duration
I would like your help with a problem i am having, i have a test database (i'm the only one using it), i was asked to run a 10000 contract automatic invoice batch, i used SQL profile to see the ...
1
vote
1answer
49 views
Is there anything wrong with splitting a complicated view into many small ones?
I have a view where each row is associated with a customer, and the columns are various computed values such as life_time_value and purchases_per_week, as well as more complicated statistical values ...
0
votes
1answer
24 views
can't find column stats for newly created table
I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.
database current settings are:
I started with ...
0
votes
1answer
44 views
Does plan recompilation happens to plan guide?
Does plan recompilation happens for plan guide due to the, for example, auto update stats?
P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
1
vote
1answer
43 views
How to prevent seek pagination from doing sequential scan over entire table
I have 3 tables and a materialized view:
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
...
0
votes
1answer
22 views
Query processor ran out of internal resources and could not produce a query plan” - MariaDB
i have some query
SELECT COUNT(loan_payment) as setcolumn
FROM table1
WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'}
and period_code = 'MONTHLY'
AND company_id =...
0
votes
1answer
24 views
Postgres foreign data wrappers weird join behavior
So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.
Keep in mind the following snippets are ...
1
vote
2answers
67 views
SQL Server order of execution and query plan
I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc):
FROM, JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
However, I cannot understand how in the query plan the sort (...
7
votes
1answer
216 views
GROUPING SETS returning unexpected results with calculated expression
Here we have two similar queries using grouping sets
where the SELECT clause contains some expressions calculated in aggregation:
SELECT RN10, RN10 / 10, COUNT(*) FROM
(
SELECT RN, RN/10 AS ...
13
votes
1answer
1k views
SQL Server Spill level 0 - what is it?
I was asked to look into performance of a query in SQL Server 2016 SP2, and I found something that I have not seen before:
<Warnings>
<SpillToTempDb SpillLevel="0" SpilledThreadCount="1" /&...
-2
votes
1answer
28 views
Clustered index scan in Delete query
I am trying to improve the performance of the delete query below. #ROW_IDS.Column_ID and Table1.ID are big integer values.Table1.ID and #ROW_IDS.Column_ID are clustered indexes. Table1 has 131 million ...
5
votes
2answers
91 views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
1
vote
1answer
24 views
Query performance improvement
Can I improve performance of this query?
SELECT transact.id
FROM transaction0 transact
JOIN journ_notes journ ON transact.journal_jdoid = journ.jdoid
JOIN product ...
1
vote
1answer
50 views
Weird planning time data dependency in Postgres
We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms ...
0
votes
1answer
43 views
Why scan clustered index instead seek nonclustered index?
There is SQL script:
CREATE TABLE users
(
id INT,
firstname VARCHAR(50),
surname VARCHAR(50)
);
CREATE CLUSTERED INDEX ix_users_id
ON users (id);
CREATE ...
2
votes
1answer
78 views
Query planner enerates different plans in psql and application for the same query
I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed.
I want to identify ...
0
votes
1answer
14 views
What does below parallel processing execution plan mean?
I have a table tbl_a with 200 k records. I wrote a query to bring sum of id (int) column. Now execution plans shows 2 workers launched and below that parallel sequential scan on tbl_a is shown 3 times;...
4
votes
1answer
84 views
Understand why the query plan was changed [duplicate]
We had some performance issues with our ERP system. Somewhere around 06:00 the problem started. Long story short; one of my colleagues executed a DBCC FREEPROCCACHE (I know that this is not a ...
0
votes
1answer
59 views
Understanding “bitmap heap scan” and “seq scan”
I'm trying to understand that which scan is efficient, Here is the explain for the query,
EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS ...
0
votes
1answer
70 views
Querying a table with 200+ million rows by filtering on the clustered index AND additional column is super slow
We have one table that has clustered index on two columns: first one is NVARCHAR(50) and second one is on DATETIME2(3) type.
There are few other columns. When we query the table with WHERE clause on ...
1
vote
1answer
20 views
Can I tell mariadb which query plan to use for a given query?
I have a query which gets different query plans over time and one of them takes less then a minute to execute, the other one takes days. Can I tell the db to use a given plan (taken from explain or ...
1
vote
1answer
43 views
why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?
I am running the following query on MS SQL Server 2014 (and also on 2014SP3)
SET NOCOUNT ON
CREATE TABLE #GUIDs(
PartyNames_GUID UNIQUEIDENTIFIER NULL,
Party_GUID UNIQUEIDENTIFIER NULL,
...
0
votes
1answer
49 views
Query performance discrepancy between Azure and on premise SQL Server
I have the same database on both SQL Azure and on premise. Most of my queries run in similar time on the two instances but I have a handful of queries where the performance difference is on the order ...
0
votes
0answers
42 views
adding case statement in where clause leads to poor plan choice
We have a large number of procedures that use case statements in where clauses--essentially, we want to run the same procedures at different times, and we set "flags" in the database to change the ...
0
votes
0answers
28 views
SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)? [duplicate]
Let's assume we have following query (greatly simplified, of course):
SELECT * FROM MyTable
Where A or B
The query takes about 30 seconds to run
When I rewrite the query as
SELECT DISTINCT * FROM
...
0
votes
0answers
65 views
MongoDB Explain plan - elapsed time breakdown for executionStats
I have run an explain plan for a query, and am a bit confused with the timings of each stage, and how they aggregate to the total elapsed time.
Following command was run to generate the statistics, ...
1
vote
0answers
22 views
DELETE a single row from a table with CASCADE DELETE picks a slow plan… but not always
Schema Summary
A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed.
This looks like ...
16
votes
2answers
3k views
SQL estimates are way off on DELETE statement with Triggers on huge tables
I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
1
vote
1answer
53 views
Memory Grant Warning on what appears to be a better query?
OK, I was looking at potentially reducing this (as more of an exercise than a need mind you) and it seems that Im getting this warning on what appears to be a better performing query and not on the ...
0
votes
2answers
76 views
Help with execution plan analysis
I need some help interpreting this execution plan -
https://www.brentozar.com/pastetheplan/?id=SJ5NYLLSL
Whenever this query is ran it uses up a huge amount of CPU and kills the server.
I have ...
0
votes
1answer
111 views
Unable to get actual execution plan for SQL Server 2014 SP3
I was reading this blog post https://techcommunity.microsoft.com/t5/sql-server/using-xevents-to-capture-an-actual-execution-plan/ba-p/392136
and based on same created extended event
CREATE EVENT ...
1
vote
2answers
113 views
Performance - Looking for Some Advice on Improving this Query
I've been looking into a performance issue for the last few days and although I've improved the query runtime from 48 seconds to 11 seconds, I feel that it can be improved even further. The issue is ...
5
votes
2answers
299 views
Oracle does not use transitive closure to add additional predicate
Presented with a query like this:
select *
from a, b
where a.val = b.val
and a.val = 1
Oracle optimizer can use transitive closure property and combine two predicates a.val = b.val and a.val = 1 to ...
0
votes
0answers
32 views
How can I tell which queries and nonclustered indexes are doing bookmark lookups on my primary key for a specific table?
Is it possible to get the queries and the names of the nonclustered indexes in my database which are doing bookmark lookups / key lookups in the execution plans?
Would I have to go through the plan ...
0
votes
0answers
38 views
Different execution plan - Delete TOP 10 vs Delete TOP 1000 [duplicate]
Was troubleshooting a delete statement that periodically delete old data from a monitoring table and noticed different behaviour with different TOP numbers and wanted to understand why.
Delete ...
1
vote
1answer
69 views
Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries?
Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the ...
4
votes
4answers
266 views
Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR
I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:
SELECT *
FROM Users u
LEFT JOIN Comments c
ON u.Id = c....
3
votes
0answers
93 views
PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice
This is the story if a nightmare... I have this table:
Table "subscriptions"
Column | Type | Collation | Nullable | ...
2
votes
1answer
76 views
Different execution plans in SQL Server for same query but different offset
I am doing performance experiments regarding the fulltext search feature of Microsoft SQL Server using the stackoverflow database.
I have a (seemingly simple) query that results in different ...
0
votes
1answer
41 views
PostgreSQL bad plan: the frequency of a tag depends on the project!
I try to simplify a problem that I've been investigating for weeks.
Suppose that you have a large table like this:
SUBSCRIBERS
----------------------------
id | project_id | tags | ...
--------------...
