Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
2
votes
2answers
91 views
Archiving process not running fast enough
I am archiving data from one database to another database on a different SQL server.We are archiving multiple tables in our database.Recently our inserts into the source database has increased and ...
-1
votes
1answer
56 views
Job with a cursor is abnormally slow [on hold]
I have a query that takes more than 9 hours to complete.
Looking at the execution plan, I find this high-cost operator (query plan link):
How can I improve this job?
-2
votes
1answer
22 views
Azure SQL Server - big query choosing bad execution plan [closed]
I'm using Azure SQL Server v12 and I have big, complex query that runs ok but when I add more joins, it doesn't matter which table I'm joining to, it gets super slow. The important thing to point out ...
1
vote
1answer
34 views
Why are some count queries so slow?
I have a table users with 2.6 millions of rows and SELECT COUNT(*) FROM users takes 2 seconds to succeed, but request for table stories with 5.2 millions of rows takes more than an hour.
Explains are ...
6
votes
2answers
879 views
On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?
we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to ...
0
votes
0answers
8 views
How to optimise PostgreSQL planning time
I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2
select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);
that is slower than I like for where it is ...
1
vote
1answer
74 views
Redundant fields in ORDER BY for efficient sorting?
Suppose I have a table SortTest with fields Data1, Data2, Sort1, and Sort2, Key1 and Key2 need to perform the following query:
SELECT TOP 1
Data1,
Data2
FROM SortTest
WHERE Key1 = @key1 AND
...
-3
votes
0answers
67 views
same database, same schema and data, same query but different execution plans [closed]
I executed the same query against the same databases with exactly the same schema and data but it yields a completely different execution plan.
Only difference I wanted to point out is in regards to ...
-3
votes
1answer
61 views
Setting Cardinality Estimator in SQL Server 2016 for performance & using old CE of SQL Server 2012
Post upgrade to SQL Server 2016, we saw a lot of I/O issues followed by slowness which affected the whole system.
Upon investigation, we found that top queries are delete statements.
Execution plans ...
5
votes
1answer
45 views
QueryStore plan forcing limitations
I have a DELETE statement running against a table with a full-text indexed column, a handful of foreign keys with cascade enabled. It looks like this:
DELETE FROM dbo.STUDENTS WHERE STUDENTID=@...
2
votes
1answer
64 views
SQL Server expensive nested loops join and lazy table spool
I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:
select distinct d.documentpath as path, d.documentname as name, d....
8
votes
3answers
174 views
How can I get running totals of recent rows faster?
I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
0
votes
1answer
45 views
Execution plan for staging In-Memory tables estimate row count =1
We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process.
the nature of these staging tables is empty most of the time.
The ETL process executed ...
2
votes
1answer
96 views
Is it possible to force an adaptive join with a query hint?
Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.
I have a parameterised ...
1
vote
0answers
23 views
Postgres Row Level Security policy optimizes poorly compared to inline version
I have a query which looks like this:
SELECT post.id, post.author_id, post.published_at, post.content
FROM post
WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
LIMIT 5;
...
2
votes
1answer
63 views
sp_blitzCache against one stored procedure
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.
I have a copy of the output of ...
2
votes
1answer
46 views
Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- create objects
CREATE DATABASE ConvertTest
GO
USE ConvertTest
GO
CREATE ...
3
votes
2answers
46 views
Disable a bad SQL Server Query Plan vs. Force a good plan
I like to leave query planning up to the database as much as possible, and only force when absolutely necessary. I'll usually do this in SSMS with the 'Force plan' option.
So below, my purple query ...
7
votes
2answers
103 views
SQL Server creates different plans when OR condition is re-arranged
I was reviewing an under-performing query which looks like this:
WHERE manymany.Active = -1
AND manymany.Check1 = -1
AND manymany.WebsiteID = @P1
AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany....
0
votes
0answers
42 views
MS SQL 2017: Generate estimate execution plan take more than 3 min
Sometimes generate Execution Plan (estimated) for simple query take ~3min
Query:
SELECT MAX(field) FROM table_name
WHERE Org = x and PartKey = y
The table is columnstore cluster index
SQL Server ...
4
votes
1answer
72 views
Seeking Help Optimizing Self-Joins
This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the ...
2
votes
0answers
68 views
Why does the location of a join change performance? [duplicate]
I came across a performance issue and can’t quite understand what causes it. It has to do with the order in which the joins are done in my query. The predicates on the join is always the same, but ...
4
votes
1answer
235 views
Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?
I am trying to understand the effect that forcing the update of statistics with a fullscan has on execution plan estimates.
I currently have the following result in the execution plan for a very ...
17
votes
1answer
1k views
Single-row INSERT…SELECT much slower than separate SELECT
Given the following heap table with 400 rows numbered from 1 to 400:
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
...
2
votes
1answer
36 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 ...
0
votes
1answer
55 views
Different manifestations of the same logic yield very different execution times [closed]
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
47 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
33 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
22 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
118 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
328 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
121 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
73 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
28 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
35 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
254 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 ...
3
votes
1answer
83 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
43 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
218 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
36 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 ...
19
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
347 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
99 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
200 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
68 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
91 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
43 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. ...
