All Questions
Tagged with sql-server-2017 execution-plan
23 questions
-2
votes
1
answer
819
views
when update large table it take too much time to update
I work on sql server 2019
when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update
estimated execution plan
actual execution plan
Update statement takes 26.5 ...
- 147
2
votes
2
answers
51
views
when make select to top 5 of rows from table it take too much time?
I work on sql server 2019 i face issue when make select top 5 rows it take too much time .
Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows .
when run statment select ...
- 147
2
votes
1
answer
54
views
Speed the response time of the Query
So I have this query that I am using on an API endpoint, the query takes between 3-4 seconds to run, returning a small OFFSET of 10 rows. Is there a way I can put indexes somewhere or improve on ...
- 21
1
vote
1
answer
737
views
Why is Index causing bad estimation and execution plan
SELECT ISNULL(SUM(CASE WHEN T4.f_in =12 THEN T4.f_money END),0) -
ISNULL(SUM(CASE WHEN T4.f_out=12 THEN T4.f_money END),0)
FROM [Credit] M WITH(NOLOCK)
INNER JOIN [Transaction] T4 WITH(...
- 195
1
vote
0
answers
174
views
DELETE a single row from a table with CASCADE DELETE picks a slow plan... but not always
Schema Summary
A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed.
This looks like ...
- 11
11
votes
2
answers
944
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....
- 86.5k
1
vote
3
answers
2k
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.
Arguments ...
- 111
2
votes
1
answer
482
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
...
- 13.6k
7
votes
3
answers
606
views
No partition elimination for partition column values in a lookup table?
I created a partitioned table (as shown below), and seeded 480 million rows - about 181 rows per account.
I'm running baseline queries before adding indexes. I was surprised to see that doing date ...
- 83
2
votes
1
answer
437
views
How can I make this execution plan more efficient?
I have worked out all the implicit conversions, but I still see mentions of it in the plan. I have attached the plan, and any recommendation will help.
select cardholder_index, sum(value) as [RxCost] ...
7
votes
2
answers
636
views
can anyone help me with this awful query plan?
The query:
SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11,...
9
votes
2
answers
513
views
How can I get rid of an unhelpful parallel branch when unpivoting a single row?
Consider the following query that unpivots a few handfuls of scalar aggregates:
SELECT A, B
FROM (
SELECT
MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
, MAX(CASE WHEN ID = 2 THEN 1 ...
- 32.6k
0
votes
1
answer
140
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 ...
- 193
0
votes
0
answers
46
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 ...
- 193
4
votes
1
answer
478
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 ...
- 175
0
votes
1
answer
68
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 ...
- 409
12
votes
2
answers
3k
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 ...
- 230
11
votes
2
answers
1k
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 ...
- 257
11
votes
1
answer
761
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 ...
- 113
10
votes
1
answer
8k
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,239
6
votes
1
answer
1k
views
SQL Server chooses Nested Loop join with dimensional table and make seek for each row
I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.
The Sort operation Estimation is 100 rows ...
- 193
6
votes
1
answer
692
views
Actual rows read in a table scan is multiplied by the number of threads used for the scan
I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan ...
- 983
3
votes
1
answer
4k
views
Hash Match Spill
I've got a hash match spill going on here.
I've updated statistics with FULLSCAN on the tables involved so it isn't that.
Any pointers much appreciated.
https://www.brentozar.com/pastetheplan/?id=...
- 85