Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
765
questions
0
votes
1answer
13 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 ...
-1
votes
0answers
27 views
Insert into select 2 million row takes 1 hour sql server [on hold]
I am trying to insert into a table with select which returns around 2 million rows, this insert takes an hour, the query has just one where clause which is a date. nothing else.The table has 84 ...
3
votes
1answer
131 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
113 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
27 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
47 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 ...
13
votes
2answers
792 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
22 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
34 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
37 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
95 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
95 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
95 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
33 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.
...
0
votes
1answer
57 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
51 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
66 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
51 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
408 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
93 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
74 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
42 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
9 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
28 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
79 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
114 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
92 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
138 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
24 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 ...
1
vote
1answer
37 views
how to override a table's cardinality or statistics histogram
I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
16
votes
2answers
857 views
CROSS APPLY produces outer join
In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT ...
7
votes
2answers
135 views
What is the purpose of this Uniq1002 column in this index scan?
Take the following repro:
USE tempdb;
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
id int NOT NULL
PRIMARY KEY
NONCLUSTERED
...
3
votes
1answer
74 views
Hash Match Spills and Key Lookup causing major performance issue
I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I ...
1
vote
0answers
53 views
Converting show-plan XML to graphical view plan
I am using the performance dashboard to set up baseline monitoring for my inventory of SQL servers. As per the performance dashboard available within SSMS, when i click on any query to view the plan ...
4
votes
1answer
83 views
How to control execution plans in SQL Server
I've faced a problem with SQL Server generating poor execution plans.
I have 3 databases with the same structure and the same set of procedures.
My query looks like this:
select
d.Id as DirectoryId,...
3
votes
2answers
172 views
why it is doing index seek
I created a sample table as below
CREATE TABLE [dbo].[StatisticsDemo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]
Then I inserted below data as below:
...
0
votes
1answer
23 views
Analyzing a query plan for a query with TWO predicates, but index only has ONE
Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.
One homework question I'm doing ...
4
votes
1answer
302 views
Forcing execution plan with local join hints
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
4
votes
1answer
119 views
Why does MSSQL choose scan in execution plan?
Here is my query (It is a Microsoft Axapta Query):
(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1....
8
votes
1answer
343 views
What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?
So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
4
votes
1answer
120 views
Why query cache plan kept dynamic queries but not sp?
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our ...
5
votes
2answers
244 views
How to get SEEK accessing converted ID via view
Assume I have a table:
-- just for test purposes
CREATE TABLE SomeTable (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
,SomeColumn1 NVARCHAR(50) NULL
,...
7
votes
2answers
607 views
can anyone help me with this awful query plan?
The query:
SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11,...
9
votes
2answers
453 views
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ...
2
votes
2answers
120 views
Archiving process not running fast enough
I am archiving data from one database to another database on a different SQL server.We are archiving multiple tables in our database.Recently our inserts into the source database has increased and ...
0
votes
1answer
63 views
Job with a cursor is abnormally slow [closed]
I have a query that takes more than 9 hours to complete.
Looking at the execution plan, I find this high-cost operator (query plan link):
How can I improve this job?
-2
votes
1answer
38 views
Azure SQL Server - big query choosing bad execution plan [closed]
I'm using Azure SQL Server v12 and I have big, complex query that runs ok but when I add more joins, it doesn't matter which table I'm joining to, it gets super slow. The important thing to point out ...
