Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
801
questions
4
votes
2answers
622 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
567 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....
3
votes
1answer
23 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
72 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
57 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
46 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 ...
0
votes
1answer
76 views
How to use index for simple time range join?
Updated 28.11.2019
In the original question I had used the same 2 ranges. Now the examples use 2 different time ranges.
Question
I have a large (~100 million rows) timeseries table t_16 where the ...
2
votes
2answers
68 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 ...
6
votes
2answers
112 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);
...
0
votes
1answer
29 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.)
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
33 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 ...
6
votes
1answer
90 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 ...
7
votes
1answer
470 views
Is there a reliable way to determine how long it took the optimizer to generate a query plan?
I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
9
votes
1answer
195 views
Is it possible to re-use a query plan from the plan cache from one database in another database?
For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...
1
vote
1answer
37 views
How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time
I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory ...
9
votes
2answers
492 views
Xml index, slow count of rows
I am not getting index seek in the following scenario.
Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
1
vote
1answer
40 views
Query plan for a dynamically built `query_int` using `format()`
To make things simple here is a statically built version of the query that runs perfectly fast:
SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20;
The catch ...
1
vote
2answers
132 views
Why won't SQL Server 2012 use my “better” execution plan when provided via the USE PLAN hint or through a stored query plan?
I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done ...
1
vote
3answers
92 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
38 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
84 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
96 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
71 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
40 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
63 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,
...
4
votes
3answers
430 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 ...
0
votes
2answers
121 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
48 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
50 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
427 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
401 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
169 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
223 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],
...
14
votes
1answer
408 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 ...
asked Aug 29 at 22:54
5
votes
1answer
51 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
39 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
156 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
147 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
40 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
57 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
956 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
82 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
64 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
162 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 ...
asked Jul 17 at 20:47
1
vote
1answer
105 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
327 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.
...
