Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
0
votes
1answer
41 views
Cannot find slow (frozen) running query in Query Store
I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
5
votes
2answers
105 views
Very similar queries, vastly different performance
I have two very similar queries
First query:
SELECT count(*)
FROM Audits a
JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE
ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
...
0
votes
2answers
19 views
MySQL compare values of two large tables
On MySQL 5.7 I want to get the records from table 1 that are not present in table 2.
I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why.
SELECT * FROM t1 ...
1
vote
1answer
31 views
how to override a table's cardinality or statistics histogram
I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query. Can someone explain how ...
0
votes
0answers
18 views
Postgresql using hash join with small table [closed]
I have one view (B) that returns ~20M records and a table (A) that has ~50M records. If I do A inner join B ON A.id=B.id, it performs a hash join, as expected. If I do B WHERE B.id IN ('value','value',...
16
votes
2answers
760 views
CROSS APPLY produces outer join
In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():
SELECT *
FROM #MyTable AS mt
CROSS APPLY ( SELECT ...
7
votes
2answers
123 views
What is the purpose of this Uniq1002 column in this index scan?
Take the following repro:
USE tempdb;
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
id int NOT NULL
PRIMARY KEY
NONCLUSTERED
...
2
votes
1answer
60 views
Hash Match Spills and Key Lookup causing major performance issue
I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I ...
1
vote
0answers
46 views
Converting show-plan XML to graphical view plan
I am using the performance dashboard to set up baseline monitoring for my inventory of SQL servers. As per the performance dashboard available within SSMS, when i click on any query to view the plan ...
4
votes
1answer
79 views
How to control execution plans in SQL Server
I've faced a problem with SQL Server generating poor execution plans.
I have 3 databases with the same structure and the same set of procedures.
My query looks like this:
select
d.Id as DirectoryId,...
3
votes
2answers
164 views
why it is doing index seek
I created a sample table as below
CREATE TABLE [dbo].[StatisticsDemo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]
Then I inserted below data as below:
...
0
votes
1answer
23 views
Analyzing a query plan for a query with TWO predicates, but index only has ONE
Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL.
One homework question I'm doing ...
4
votes
1answer
282 views
Forcing execution plan with local join hints
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
4
votes
1answer
93 views
Why does MSSQL choose scan in execution plan?
Here is my query (It is a Microsoft Axapta Query):
(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1....
8
votes
1answer
294 views
What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?
So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
4
votes
1answer
107 views
Why query cache plan kept dynamic queries but not sp?
My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our ...
5
votes
2answers
242 views
How to get SEEK accessing converted ID via view
Assume I have a table:
-- just for test purposes
CREATE TABLE SomeTable (
ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED
,SomeColumn1 NVARCHAR(50) NULL
,...
7
votes
2answers
604 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
2answers
448 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 ...
2
votes
2answers
118 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 ...
0
votes
1answer
60 views
Job with a cursor is abnormally slow [closed]
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
28 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
59 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 ...
7
votes
2answers
912 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
10 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
76 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
1answer
68 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 ...
6
votes
1answer
62 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
96 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
194 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
48 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
104 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 ...
4
votes
1answer
79 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
83 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
65 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
57 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
3answers
122 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
2answers
87 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 ...
3
votes
0answers
70 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
251 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'
...
3
votes
1answer
40 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
58 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
38 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
35 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
129 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
368 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 ...
