A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
17 views

How can I see the plan for ONE statement in a stored procedure?

I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes. Now I've wrapped it in a stored procedure (with no parameters), the performance ...
16
votes
1answer
12k views

Operator used tempdb to spill data during execution with spill level 2

I am struggling to minimise the cost of sort operation on a query plan with the warning Operator usedtempdbto spill data during execution with spill level 2 I have found several posts related to ...
0
votes
1answer
420 views

What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached ...
1
vote
2answers
36 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
-1
votes
0answers
53 views

Query optimization (Index spool)

I have a query that need to be optimized. You can see the query plan here There you can see that 85% of the cost belows to an Index Spool but I cannot find the way to optimize it. There is a ...
0
votes
2answers
1k views

Current Active Session on a Oracle Table

I'm running a query which updates an Oracle table through Datastage. The query is taking a long time to execute so I want to examine the query/session on that Oracle table in the database and ...
11
votes
3answers
5k views

How does one investigate the performance of a BULK INSERT statement?

I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
0
votes
1answer
57 views

What causes sql server to generate a new plan for a stored procedure?

We've ran into an issue in production a couple of times now where it looks like a stored procedure gets a new, bad plan and starts causing havoc. Unfortunately, we weren't able to capture the plan ...
3
votes
1answer
136 views

What Triggers sp_recompile in database without triggers, stored procedures, etc?

In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly ...
0
votes
1answer
63 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
1
vote
1answer
420 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
97 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
1
vote
1answer
56 views

Slow SQL Server Query

First let me start off by saying I am in no way a DB expert. I know enough to be dangerous, but I am primarily a c# developer. I have a query off a view that is slow, but I am having problems figuring ...
-1
votes
1answer
42 views

Index Update with Eager Spool and Sort operators in Execution Plan

After an UPDATE statement on a large table, the execution plan shows updates of indexes (all non-clustered) that include the updated columns. Before each index update, there is an Eager Spool ...
-1
votes
1answer
1k views

Why are SQL execution plans generally interpreted right to left? [closed]

Why are SQL query/execution plans generally interpreted right to left? English is a left-to-right written language. Even if for some reason SQL was working out the plan backwards, the data is still ...
1
vote
1answer
98 views

How to use index for simple time range join?

I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp. This is a follow up of this question: Why does a CTE ...
1
vote
1answer
525 views

Window aggregate resulting in slow query on postgres 9.6

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query: explain analyze SELECT * FROM ( SELECT A.person_id, O.session_id, timestamp_ AS ...
8
votes
1answer
2k views

Bad execution plan after stats update due to temp table

A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters. Issue seems to come from a ...
0
votes
1answer
196 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
2
votes
1answer
246 views

Inefficient execution plan selected by postgresql

I have the following SQL query explain analyze select CERTIFICATE_ID, SERIAL_NUMBER, SUBJECT_DISTINGUISHED_NAME, NOT_BEFORE, NOT_AFTER, FIRST_SEEN, REVOKED, LINT_ERRORS from (...
3
votes
1answer
176 views

Implicit conversion causing error part of the time

I have been scratching my head about this one for the last day or so - I just can't see why a procedure works in one environment, but then fails in another due to a conversion error (same data, same ...
4
votes
2answers
649 views

Query execution plan is horrible until Statistics are Updated

I hope you guys can help me here. Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They ...
11
votes
2answers
591 views

Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Setup DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys....
0
votes
3answers
123 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
3
votes
1answer
33 views

Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Using PostgreSQL 11, I have the following table with around 450 million rows: postgres=> \d+ sales Table "public.sales" ...
1
vote
2answers
74 views

Overestimated query resulting in poor performance - TVF with xml processing

Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for ...
0
votes
1answer
58 views

Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows

I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why ...
0
votes
1answer
55 views

Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
4
votes
1answer
2k views

Check which statistics were used in query optimization

Is it possible to tell when statistics were last accessed by a query using T-SQL? I'm trying to ascertain what statistics are involved in a query, I know I can check the visual query plan but I ...
2
votes
1answer
316 views

SQL Profiler plus Include Actual Execution Plan

Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler? I see Showplan XML, but it shows only the execution plan without recommended ...
2
votes
2answers
74 views

Postgresql Query plan different on two servers

I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
21
votes
1answer
1k views

Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table: CREATE TABLE [dbo].[Accounts] ( [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), -- WHATEVER other columns ); GO CREATE UNIQUE CLUSTERED INDEX [AccountsIndex] ...
2
votes
1answer
829 views

Incorrect statistics are used on index seek

I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in ...
35
votes
2answers
10k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
3
votes
2answers
114 views

Understanding Execution Plan

I am really having hard time understanding the below execution plan. Query 1: (Simple Select Query) Record count of ##SALES_UNIT_RESULT is 168. In first execution plan it is fine. ;WITH ...
1
vote
1answer
176 views

DB2 LUW: how to influence query planner's choice of join

Suppose I have two tables, A and B. They "share" the same primary key, which is indexed in both tables. Table B also has a timestamp column which is separately indexed. I do a subselect on B using ...
6
votes
2answers
114 views

Why do we have Top N Sort in this example?

In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top? use tempdb; go create table dbo.t1 (id int identity primary key, v int); ...
1
vote
1answer
163 views

MariaDB uses “wrong”/irrelevant Keys on large table

We have migrated our database from a local hosting company to AWS. Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
0
votes
1answer
31 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
8
votes
2answers
12k views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
4
votes
3answers
443 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 against this table: CREATE ...
15
votes
3answers
3k views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
14
votes
1answer
2k views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
8
votes
2answers
242 views

Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan. To reproduce this,...
11
votes
1answer
2k views

Estimated versus actual query plan with function calls

I have this query on SQL server, a merge replication query: SELECT DISTINCT b.tablenick, b.rowguid, c.generation, sys.fn_MSgeneration_downloadonly ( c.generation, ...
0
votes
1answer
23 views

How do I make Postgres use an index when using IN or ANY?

I have a query such as: select col1 from mytable where col1 in('a','b','c') But running such a query yields a Seq Scan per EXPLAIN If I have an index on col1, why does Postgres not do an index scan?...
2
votes
1answer
36 views

How to get more physical details when executing an SQL query?

I want to get some physical features about a SQL query execution with PostgreSQL, like Read/write page accesses I/O operations Response time Sequential I/O throughput rate Deadlocks Pages scanned ...
34
votes
2answers
13k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[...
6
votes
1answer
96 views

When are non-parameterized, non-trivial, adhoc query plans reused

I'm currently investigating an application that seems to generate 99% adhoc query plans against the database it is querying. I can verify this by running the following statement to retrieve a summary ...
4
votes
1answer
553 views

SQL query fills up RDS free storage space after upgrade to MySQL 5.7

After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space. The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS ...