Questions tagged [execution-plan]

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

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