Questions tagged [execution-plan]

The strategy selected by the query optimizer to process a query.

Filter by
Sorted by
Tagged with
7
votes
3answers
210 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 ...
0
votes
1answer
60 views

Is IF ELSE better in this stored procedure than a Parameter? [duplicate]

I found a stored procedure in our sql server 2016 db Create PROCEDURE XXX @complicatedQuery bit AS BEGIN SELECT some fields FROM Table A join Table B on something JOIN Giant Table C ...
0
votes
1answer
54 views

Part of parallel execution plan has serial portion

I have a big linq to sql query which runs in parallel mode. It is returning results in 8 seconds and it is pretty good. I noticed in the execution plan that the extreme right part(from where execution ...
0
votes
0answers
22 views

Building and Executing a Query Execution Plan Manually in PostgreSQL

An SQL query goes through multiple stages to be executed. This is a wide topic, but in summary, multiple execution strategies are proposed for each query, they are evaluated, and one is selected for ...
1
vote
1answer
74 views

Why is parallelism not good in this query?

I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias: Finding Key Lookups inside the Plan Cache and from there I borrowed the ...
2
votes
1answer
59 views

PostgreSQL. Unstable execution time hugely exceeding planning time

PG version: 10.9 Schema: create table core_bundle ( id serial not null constraint core_bundle_pkey primary key, dt_add timestamp with time zone not null, dt_upd timestamp with time zone ...
2
votes
1answer
413 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] ...
1
vote
1answer
217 views

Slow SQL query from app, fast from SQL Studio

I have a query that takes 30+ seconds when called from app, but takes <1sec when called directly from SQL SMS. Here is the query : SELECT T0.pk FROM products T0 WHERE (( ( T0.pk IN (...
1
vote
0answers
21 views

Understanding where/how to use the “selectivity of an equality predicate between two columns”

This lecture on "Result size estimation" defines a formula of equi-joins based on Selinger paper. result cardinality = max number of tuples possible * product of all selectivities 1 - max number of ...
2
votes
2answers
89 views

Example of query execution plan where the cardinality estimation makes a difference

I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost. ...
0
votes
1answer
51 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
0answers
43 views

MariaDB Inner join is slow

Here is my SQL (with explain): EXPLAIN SELECT * FROM GBI gbi INNER JOIN VF_KRED kred ON gbi.vendor = kred.vendor; Giving below the outcome: Now, the select SQL takes ~10 seconds to produce the ...
2
votes
1answer
35 views

How to read T-SQL time statistics?

I have a simple stored procedure as an example, which reads a table with 3 rows. CREATE PROCEDURE demo_procedure AS BEGIN SELECT * FROM my_table END When I run it with time ...
1
vote
1answer
81 views

How to improve simple update statement causing blocking issues, inneficient index scan in query plan

Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
3
votes
2answers
125 views

Index Seek scanning whole table dependent on parameter value

I have a query: SELECT Id, ColumnA, ColumnB FROM MyTable WHERE ColumnA = @varA OR ColumnB = @varB The table is defined as CREATE TABLE MyTable ( Id INT IDENTITY(...
4
votes
1answer
94 views

Large variances between two similar queries when doing WHERE EXISTS on related table

I'm trying to diagnose an issue where 2 very similar queries are resulting in very different execution times, even though the execution plans are extremely simple. Broadly (and I've trimmed the ...
0
votes
1answer
48 views

Cannot find slow (frozen) running query in Query Store [closed]

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 ...
8
votes
2answers
156 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
34 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
39 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 ...
17
votes
2answers
1k 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
146 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 ...
3
votes
1answer
92 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
163 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
98 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
186 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
48 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
325 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
128 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....
10
votes
1answer
444 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
154 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
247 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
611 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
456 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
124 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
65 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
68 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
114 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
926 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
25 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
81 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
96 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
114 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=@...
3
votes
2answers
219 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
225 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
57 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
130 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
193 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
197 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
198 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 ...