Stack Exchange Network

Stack Exchange network consists of 175 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.

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