Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
783
questions
1
vote
3answers
81 views
Filtered index is not used when variable in WHERE condition
Why MS SQL Server refuse using supporting filtered index in this scenario?
-- demo data
CREATE TABLE #Test (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
,Col1 NVARCHAR(36) ...
2
votes
0answers
32 views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
1
vote
2answers
77 views
How to check actual execution plan of a query after it has been executed
In SQL Server, how can we see actual execution plan of a query, after it has been executed?
As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is ...
1
vote
0answers
15 views
Understanding PostgreSQL Execution Plans
I'm trying to understand the motivation of PostgreSQL 12 query planner here.
Database Structure
CREATE TABLE subjects
(
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
);
CREATE TABLE ...
2
votes
1answer
91 views
How to rewrite CASE expression to short-circuit evaluation
I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?
Demo data:
CREATE TABLE #...
0
votes
1answer
65 views
Worse query performance even after improving the query by making it to run in parallel mode
I have a SQL Query which gets generated from application using EFF.
It uses a view and this view has joins to multiple tables in there.
The query runs slow and when i looked at the execution plan,i ...
2
votes
1answer
38 views
Improving the query standards and performance run time
For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i ...
0
votes
0answers
62 views
Explicit conversion to avoid the implicit conversion warning [duplicate]
I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below.
WITH X
AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus,
...
1
vote
1answer
123 views
SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week
I have a very simple query
INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC
I have a simple index on the ...
0
votes
2answers
117 views
Horribly performing query, how to fix
select
user_id,
count(id) as unread_count
from
notifications
where
is_read = false
and user_id in(select(unnest('{200 user IDs}' :: bigint[])))
group by
user_id;
Problem is, ...
-1
votes
1answer
44 views
Query Plan for a linked-server query getting cleared from plan cache after every execution
I have linked server query which runs of a view created in the remote server.
Whenever i run the query i can see that a Prepared plan gets created in the plancache.
But that gets removed from the plan ...
0
votes
1answer
44 views
Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]
I have two databases in the same SQL Server instance.
One is a test database with a smaller number of records and total size of 500GB.
Other one is production database with a size of 2.2 TB.
Both ...
15
votes
1answer
424 views
Which costing factors go into the optimizer choosing different types of spools?
Spoolum
In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries.
Read only queries, particularly on the inner ...
8
votes
2answers
339 views
Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?
I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
7
votes
1answer
155 views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
6
votes
2answers
183 views
Eliminate key lookup in execution plan
I have the following query:
DECLARE @p__linq__0 UNIQUEIDENTIFIER
SET @p__linq__0 = '... some guid ...'
SELECT TOP 1
[EventId] AS [EventId],
[DateCreated] AS [DateCreated],
...
13
votes
1answer
383 views
Forced plans on readable secondaries
If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary?
I'm looking for answers that cover both possibilities for plan forcing:
Plan Guides
Query ...
5
votes
1answer
47 views
Bad execution plan when having partial index and big In-clause in Postgres
Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements.
Given the following ...
0
votes
1answer
25 views
What exactly happens when optimizer_prune_level = 1?
This documentation link states that optimizer_prune_level
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space.
Can ...
3
votes
1answer
147 views
How can I get rid of this Lazy Spool, or otherwise improve this query's performance?
Here is the plan: https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS
I am mostly interested in how to get rid of the lazy spool?
This is the query:
SELECT DISTINCT
SM.Security_ID '...
1
vote
3answers
137 views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
...
0
votes
0answers
33 views
Postgres uses sequential scan with OR condition, but index scan with AND condition
We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
2
votes
1answer
52 views
Find SQL text from Plan Diagram in SentryOne Plan Explorer
I had SSMS create an execution plan and opened it through the context menu in SentryOne Plan Explorer. I go to Top Operations, select one with huge cost and switch to Plan Diagram. The node ...
14
votes
2answers
891 views
Constant Scan spooling
I have a table with a few dozens of rows. Simplified setup is following
CREATE TABLE #data ([Id] int, [Status] int);
INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);
And I have a ...
0
votes
1answer
23 views
Is it possible to increase Oracle execution plan output indentation?
It seems Oracle's execution plan indent each row for just one character. See below image. Is there a setting to increase this value? To me, this is not very readable.
0
votes
1answer
55 views
SSMS 2014 does not launch Graphical Execution Plan from clicking hyper-linked query plan - ShowPlanXML
I am working with an older version of Microsoft SQL Server Management Studio 2014. I run the following query:
SELECT TOP 10 Q.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan( qs....
2
votes
1answer
46 views
Graphical Actual Execution Plan - Estimated vs Actual number of rows
I am using:
SQL Server Management Studio v18.1
SQL Server 2017 CU9
AdventureWorks restored from here
Test query:
USE [AdventureWorks];
GO
SELECT TOP (13) *
FROM Sales.SalesOrderHeader AS s
...
10
votes
1answer
110 views
Hash aggregate bailout
A question that arose in a chat discussion:
I know hash join bailout switches internally to a sort of nested loops thing.
What does SQL Server do for a hash aggregate bailout (if it can happen at ...
1
vote
1answer
103 views
Slow join performance SQL Server
I am struggling a bit to improve performance on one of my queries.
What I had is basically all of those 4 tables for reporting without any indexes, PKs, etc.
I was thinking that if I copy them (...
1
vote
1answer
173 views
Query performance of index scans slower than parallel seq scan on Postgres
I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
1
vote
1answer
34 views
Search SQL Server Execution Plan by cost in SSMS
I was given a large Execution plan and I'm trying to find the nodes connected to high costs. SSMS offers a search util, however, I did not find the option that searches for the cost value in percent.
...
7
votes
3answers
199 views
No partition elimination for partition column values in a lookup table?
I created a partitioned table (as shown below), and seeded 480 million rows - about 181 rows per account.
I'm running baseline queries before adding indexes. I was surprised to see that doing date ...
0
votes
1answer
58 views
Is IF ELSE better in this stored procedure than a Parameter? [duplicate]
I found a stored procedure in our sql server 2016 db
Create PROCEDURE XXX
@complicatedQuery bit
AS
BEGIN
SELECT some fields FROM
Table A join Table B on something
JOIN Giant Table C ...
0
votes
1answer
52 views
Part of parallel execution plan has serial portion
I have a big linq to sql query which runs in parallel mode.
It is returning results in 8 seconds and it is pretty good.
I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
0answers
22 views
Building and Executing a Query Execution Plan Manually in PostgreSQL
An SQL query goes through multiple stages to be executed. This is a wide topic, but in summary, multiple execution strategies are proposed for each query, they are evaluated, and one is selected for ...
1
vote
1answer
72 views
Why is parallelism not good in this query?
I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias:
Finding Key Lookups inside the Plan Cache
and from there I borrowed the ...
2
votes
1answer
54 views
PostgreSQL. Unstable execution time hugely exceeding planning time
PG version: 10.9
Schema:
create table core_bundle
(
id serial not null constraint core_bundle_pkey primary key,
dt_add timestamp with time zone not null,
dt_upd timestamp with time zone ...
2
votes
1answer
410 views
How can I make this execution plan more efficient?
I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.
select cardholder_index, sum(value) as [RxCost] ...
1
vote
1answer
172 views
Slow SQL query from app, fast from SQL Studio
I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS.
Here is the query :
SELECT T0.pk
FROM products T0
WHERE (( ( T0.pk IN (...
1
vote
0answers
20 views
Understanding where/how to use the “selectivity of an equality predicate between two columns”
This lecture on "Result size estimation" defines a formula of equi-joins based on Selinger paper.
result cardinality = max number of tuples possible * product of all selectivities
1 - max number of ...
2
votes
2answers
81 views
Example of query execution plan where the cardinality estimation makes a difference
I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost.
...
0
votes
1answer
47 views
Postgres index not being used
Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
0answers
26 views
MariaDB Inner join is slow
Here is my SQL (with explain):
EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
gbi.vendor = kred.vendor;
Giving below the outcome:
Now, the select SQL takes ~10 seconds to produce the ...
2
votes
1answer
32 views
How to read T-SQL time statistics?
I have a simple stored procedure as an example, which reads a table with 3 rows.
CREATE PROCEDURE demo_procedure
AS
BEGIN
SELECT * FROM my_table
END
When I run it with time ...
1
vote
1answer
81 views
How to improve simple update statement causing blocking issues, inneficient index scan in query plan
Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
3
votes
2answers
119 views
Index Seek scanning whole table dependent on parameter value
I have a query:
SELECT Id,
ColumnA,
ColumnB
FROM MyTable
WHERE ColumnA = @varA OR
ColumnB = @varB
The table is defined as
CREATE TABLE MyTable
(
Id INT IDENTITY(...
4
votes
1answer
93 views
Large variances between two similar queries when doing WHERE EXISTS on related table
I'm trying to diagnose an issue where 2 very similar queries are resulting in very different execution times, even though the execution plans are extremely simple.
Broadly (and I've trimmed the ...
0
votes
1answer
48 views
Cannot find slow (frozen) running query in Query Store [closed]
I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
8
votes
2answers
147 views
Very similar queries, vastly different performance
I have two very similar queries
First query:
SELECT count(*)
FROM Audits a
JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
...
0
votes
2answers
29 views
MySQL compare values of two large tables
On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.
I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.
SELECT * FROM t1 ...
