Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [execution-plan]

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

1
vote
0answers
17 views

MSSQL: Optimize multiple JOINs with large data

I have 3 tables with this structure: +-----------+ +-----------+ +-----------+ | Files | | Programs | | Channels | |-----------| |-----------| |-----------| | ID | | ID ...
1
vote
0answers
16 views

SELECT very slow when JOIN and LIMIT are specified but no/few results found

I am running into an issue where the query planner does not take into account how a JOIN effects the frequency of particular values. For some context: I am storing sessions in an append only log ...
-1
votes
0answers
32 views

Resources for learning how to read query plans [on hold]

Most posts on StackExchange DBA assume that readers can understand query plans. What is the best resource (book, website, video tutorial, etc.) for learning how to read and understand query plans? ...
0
votes
1answer
43 views

Different manifestations of the same logic yield very different execution times [on hold]

Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested. For every widget in the ...
1
vote
2answers
45 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
31 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
20 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 ...
3
votes
1answer
107 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
15 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
270 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 ...
5
votes
1answer
106 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
24 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
60 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
21 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
30 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
228 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 ...
2
votes
1answer
71 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
37 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
209 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
21 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 ...
17
votes
1answer
1k 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
327 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 ...
6
votes
1answer
89 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 ...
2
votes
1answer
163 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
60 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 ...
3
votes
2answers
69 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
42 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. ...
-2
votes
0answers
28 views

Stored Function suddenly slows after the first five queries

When recompiled it speeds up to ~ 200 ms for the next five then slows down afterward. The behavior is seen even when I execute the same query (with same input params) multiple times. The stored proc ...
3
votes
1answer
110 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,...
8
votes
2answers
153 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
84 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
24 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
457 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
151 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
120 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
66 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
16 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
52 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 ...
2
votes
1answer
32 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
90 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
313 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
176 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
35 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
38 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
62 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
47 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
54 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
50 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
1answer
204 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 ...