We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
56 views

Why is a COUNT query faster than a result set query?

Suppose I have two similar queries, with complex joins; query 1 returns rows and columns: SELECT col1, col2, col3, ...etc FROM MyBigTable LEFT JOIN AnotherTable1 On ... etc LEFT JOIN ...
1
vote
1answer
52 views

How to get EstimatedRows values from SQL Server execution plan nodes

When I execute the following code in an Azure SQL Database (v12) or SQL Server 2016 instance, I get values in the EstimatedRowsRead column, but not in the EstimatedRows column, and I'm not seeing why. ...
0
votes
0answers
53 views

Sql Server - Execution Plan - Query Cost (relative to batch) calculation [duplicate]

We have a Table Value function which: Creates and Populates a table variable (@Temp) from a SELECT Runs two different UPDATE statement to change values on 2 different columns in @Temp Populates the ...
4
votes
1answer
162 views

How to find inputs that caused different query plans in Query Store

Query store shows that a completely parameterized query has multiple query plans: How can I find the input that is causing such long response times? Why would the same parameterized query end up ...
0
votes
0answers
17 views

any knowledge or experience with dynamic ax [duplicate]

I support a system called "dynamics ax" and sometimes my optimizer picks a good plan, drops it and then picks a bad plan (where the same code is been ran). users who usually wait for 10 seconds start ...
1
vote
3answers
456 views

SQL Server statement takes forever while running instantly in Oracle

Please help me interpreting this statement and plan: https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV We migrated from Oracle to SQL Server and we have some quite strange behaviours. It might be ...
6
votes
1answer
139 views

Why doesn't first execution plan use RowCount Spool?

Here is my repro that was tested on SQL Server 2008 R2, 2012, 2016. The second and the third queries do use RowCount Spool, why the first does not? create table dbo.t (id int identity primary key, v ...
11
votes
2answers
1k views

Why does a DELETE query run in one format much longer than in another?

I have specific cleanup code that tries to remove some duplicates. This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query: DELETE ...
2
votes
0answers
27 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
1
vote
2answers
148 views

Warning in Execution Plan

I have a T-SQL query with four inner joins, I executed the query in SSMS with Actual Execution Plan selected. When I review the generated plan I saw the warning icon on Sort operator. What is the ...
0
votes
1answer
91 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
1
vote
0answers
71 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
6
votes
1answer
330 views

Execution plan doesn't show memory grant

I installed SQL Server 2019 on a VM on Azure (DS11). I am trying to illustrate adaptive query processing using this post here. The issue is that when I hit CTRL+L in SQL Server Management Studio to ...
4
votes
1answer
106 views

Reading 300% rows - Problematic execution plan

Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4 I am struggling with a query that uses a lot of time. The obvious reason for this is wrong estimates of amount of rows....
2
votes
1answer
50 views

Why does elimination of an if-statement cause notable speed up in my function?

I have the following table: CREATE TABLE [dbo].[StateChanges] ( [RecordId] uniqueidentifier NOT NULL, [LinkedObjectId] uniqueidentifier NOT NULL, [ChangeType] TINYINT not null, [...
4
votes
1answer
274 views

Covering Index Changes Execution Plan but is not used

I have the following occasionally slow running query: SELECT C.CustomerID FROM dbo.Customers C WITH (NOLOCK) WHERE C.Forename = @Forename AND C.Surname = @Surname OPTION (RECOMPILE) CustomerID ...
0
votes
1answer
175 views

Why Postgres uses a custom defined index instead of the default one on the primary key

I have a table called alpha with many columns, the primary key is just alpha_id. I have also an index (super_index) on that table that uses alpha_id and status, which is just another column. I run a ...
18
votes
1answer
2k views

When does SQL Server warn about an Excessive Memory Grant?

What are the conditions that produce an "Excessive Grant" execution plan warning? The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, ...
9
votes
1answer
487 views

Missing Non Clustered Index already part of Clustered Index

I'm debugging a slow running query and in the execution plan a non-clustered index is suggested, with 51.6648 Impact. However, the non-clustered index only includes columns that are already in the ...
7
votes
1answer
298 views

How to get cross apply to operate row by row on a view?

We have a view that is optimized for single item queries (200ms no parallelism): select * from OptimizedForSingleObjectIdView e2i where ObjectId = 3374700 It also works on small sets of ...
1
vote
1answer
487 views

Random insane bad execution plans in Microsoft SQL Server

Hi together I know that issue has been discussed for several times, but we face an insane weird issue on our new SQL Server 2014 SP3 right now, and we just can't get behind. We switched over from ...
0
votes
3answers
85 views

Query produces two query plans (at different times). How do I always produce the better query plan?

I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving ...
4
votes
2answers
173 views

Same query hash for totally different queries

Query_hash definition from sys.dm_exec_query_stats is: query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to ...
0
votes
1answer
80 views

what is more important on a query execution plan, cardinality or selectivity?

The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
3
votes
1answer
156 views

Execution Plan is different when Constant Variable is Null

I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes,...
11
votes
2answers
255 views

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?

I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
1
vote
1answer
527 views

Who has SHOWPLAN permissions on a database

Is there a way I can see all users in a database who currently have access to view execution plans? That is, who has SHOWPLAN permissions
0
votes
0answers
104 views

Postgresql explain cost vs actual database CPU usage

Let's suppose there are two queries A and B getting the same result. explain analyze A cost: 10000 execution time: 2 sec explain analyze B cost: 20000 execution time: 1 sec # Though it is not exact ...
12
votes
1answer
504 views

Strange query plan when using OR in JOIN clause - Constant scan for every row in table

I'm trying to produce a sample query plan to show why UNIONing two result-sets can be better than using OR in a JOIN clause. A query plan I've written has me stumped. I'm using the StackOverflow ...
7
votes
3answers
387 views

Field = Parameter OR Parameter IS NULL Pattern

I am aware of the parameter sniffing issues associated with stored procedures written with a predicate like the following: CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN; SELECT [...
4
votes
2answers
1k views

Optimizing table valued function SQL Server

I am trying to optimize this table-valued function. If I could, I would change it to the procedure but I cant. The problem is with two update statements. I kept only those two in the function because ...
3
votes
1answer
72 views

There's a long delay in this query that I need to resolve [closed]

This is a multi-tenant/portal DNN (formally DotNetNuke) database, and a core query as part of the registration process seems to be terribly slow. Here it is: declare @PortalID int = 0; SELECT R.*, ...
0
votes
0answers
33 views

Compare PostgreSQL Execution Plans

PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
3
votes
0answers
111 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 ...
3
votes
1answer
50 views

Why are there multiple plans for query under forced parameterization?

I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store: delete from [...
4
votes
1answer
114 views

High processor utilization when running a stored procedure

I have a stored procedure which was running fine till 12/11 with average execution time under 2 hours. From Tuesday (12/11) onward, the stored procedure is running continuously for more than 3 hours ...
6
votes
1answer
688 views

Why would a Clustered Index Seek return a higher “Actual Number of Rows” than there are rows in the underlying table?

I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, ...
3
votes
1answer
214 views

Unique filtered index and surprising estimation

When I view the estimated execution plan for this query... SELECT * FROM tabela2 x LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod ... the Estimated Number of Rows property for [table1]...
2
votes
0answers
48 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
1
vote
1answer
137 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 ...
1
vote
2answers
74 views

SQL Server Execution Plan on server level

Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I ...
2
votes
1answer
58 views

Efficient predictable ordering in PostgreSQL

I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I ...
1
vote
0answers
84 views

Converting an XML field to nvarchar causes warning on main SELECT in explain plan

I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following: SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p This ...
0
votes
0answers
198 views

when to use Simple or Forced Parameterization in my databases?

I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it. it turned out ...
2
votes
2answers
4k views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
2
votes
1answer
717 views

execution plan TABLE ACCESS BY INDEX ROWID cardinality

This is the explain plan for a certain query. The query contains a PK order by clauses and matches on two other columns on the where clause. Since PK is used for index full scan, I assume cardinality ...
0
votes
1answer
68 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 ...
8
votes
1answer
2k views

“Warnings: Operation caused residual I/O” versus key lookups

I've seen this warning in SQL Server 2017 execution plans: Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40. ...
2
votes
2answers
151 views

Force SQL Engine to prepare Non-Null plan only for a query that use variable

I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours. Approach 1 SELECT *, SUBSTRING(IDAndCode,CHARINDEX ('$', ...
3
votes
1answer
501 views

Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries

Issue: I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type. ...