Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
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 ...