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 ...
