We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
13
votes
1answer
571 views

Why is PostgreSQL choosing the more expensive join order?

PostgreSQL using defaults, plus default_statistics_target=1000 random_page_cost=1.5 Version PostgreSQL 10.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 6.4.0) 6.4.0, 64-bit I've vacuumed and ...
0
votes
0answers
58 views

Cannot find query in plan cache and query execution times different for different paramters

I am investigating the reason why a query is performing bad in my SQL server 2016. The query executes in 1 sec when i give the date range filter as 2017-06-21 and 2018-06-20. When i give the date ...
6
votes
2answers
205 views

High IO on index, is there a more performant solution?

I've got this query which used to take minutes to run and now takes like 6 seconds but it runs a thousands of time a day so I'd like to make it faster. https://www.brentozar.com/pastetheplan/?id=...
2
votes
1answer
324 views

What does “Rows Removed by Index Recheck” without “lossy” mean?

Here's part of an EXPLAIN ANALYZE from Postgres 9.6: -> Bitmap Heap Scan on cities (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1) Recheck Cond: (...
32
votes
2answers
3k views

If a CTE is defined in a query and is never used, does it make a sound?

Do unused CTEs in queries affect performance and / or alter the generated query plan?
1
vote
1answer
348 views

Poor performance by LINQ to SQL generated query execution plan

Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve. The query is generated from the .Net application using LINQ to SQL. I can see large sort ...
-4
votes
1answer
226 views

Need to know workarounds to tackle a query plan [closed]

Need to know workarounds to tackle this query plan. Tried suggestion given by Brent, but end up with the entire SP itself as topmost cost. Please let me know if I am missing anything here
3
votes
1answer
219 views

Can't find compiled parameters values in the plan cache

I have found, through the query store, a query that's performing on average 297582 logical reads. I wanted to see if I was able to tune that query a bit, and after that, try to execute the query ...
3
votes
1answer
435 views

Execution time high for Query in sp_executesql

I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below. I run the application and capture the query using profiler....
1
vote
1answer
155 views

SQL Queries Slow in the Upgraded environment

I setup and configured an new SQL database server with SQL Server 2016 Standard Edition. This is going to replace our existing SQL database server which is 2008 R2 database server. I restored recent ...
2
votes
1answer
210 views

Inefficient execution plan selected by postgresql

I have the following SQL query explain analyze select CERTIFICATE_ID, SERIAL_NUMBER, SUBJECT_DISTINGUISHED_NAME, NOT_BEFORE, NOT_AFTER, FIRST_SEEN, REVOKED, LINT_ERRORS from (...
3
votes
1answer
103 views

Why get queryplans recompiled every minute

I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated. I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to ...
2
votes
1answer
128 views

What does MySQL actually use to decide which index should be used?

I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless. In my development and use of MySQL, I ...
2
votes
2answers
89 views

Efficient SQL query to return parents that satisfy constraints on children

I've got a schema something like having an Entity table, with a to-many relationship to Attribute, which has a to-many relationship to Value. Attribute has a column Name, and Value has a column Value: ...
2
votes
1answer
313 views

Can't reduce cost of query plan and get rid of Key Lookup because of cursor

I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere . I have read here that it has ...
0
votes
0answers
35 views

Will Parameter Sniffing happen? [duplicate]

I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me ...
2
votes
1answer
267 views

SQL Profiler plus Include Actual Execution Plan

Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler? I see Showplan XML, but it shows only the execution plan without recommended ...
13
votes
1answer
1k views

Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database): film: film_id is pkey actor: actor_id is pkey film_actor: film_id and actor_id are fkeys to film/actor I am selecting a ...
1
vote
0answers
277 views

Why does this SQL Server execution plan contain a snapshot cursor for a select statement?

Here is the situation. I have a fairly simple query. It returns ~22k rows: select * from tbl_A When I run this query in SSMS (2017 7.6), I get a fairly simple plan: SELECT <= Clustered Index ...
1
vote
0answers
37 views

Impact of numerous indexes on query optimizer for a delete statement

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
31
votes
3answers
4k views

Why does SQL Server use a better execution plan when I inline the variable?

I have a SQL query that I am trying to optimize: DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962' SELECT Id, MIN(SomeTimestamp), MAX(SomeInt) FROM dbo.MyTable WHERE Id = @...
3
votes
1answer
108 views

CPU vs Elapsed & Parallelism

I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel. After typing that, I was ...
8
votes
1answer
274 views

Why does a plan with FULL optimization show simple parameterization?

I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized. Then why is this plan showing Full Optimization, and ...
5
votes
2answers
225 views

How to control position of startup predicate in the execution plan

For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows ...
5
votes
1answer
255 views

Pre-staging data causes execution plan cost to skyrocket

I have a troublesome query that we are trying to tune. One of our first thoughts was to take a portion of a larger execution plan and store those results to an intermediate temp table and then ...
0
votes
1answer
57 views

Oracle optimizer wrongly estimates cardinality as 1 for public synonyms

I have this query: SELECT d.devicename, d.template, d.deleteflag, SUM(isUnexpected(gv.value,sv.param1,sv.param2,sv.comparator,sv.operator) ) totalDeviation FROM vm_wells_situ s ...
0
votes
1answer
53 views

How to find the time taken to execute a script

As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert. Is there any way to find out the time taken to execute ...
3
votes
1answer
105 views

Degree of parallelism and scheduler usage

Is it ever possible for a SQL Server query running with an effective degree of parallelism x to have parallel workers assigned to more than x distinct schedulers? Even if the execution plan has many ...
0
votes
1answer
101 views

Subquery Applying Incorrect Predicate?

I have a query which contains a subquery. When executed on a local copy of my database it runs as normal and executes within < 1 second. I've noticed however that if I run this same query on our ...
1
vote
0answers
109 views

bit columns generating warnings - how can a cast in the field list affect the cardinality estimate?

A few days ago I had a question about warnings in the execution plan Type conversion in expression may affect “CardinalityEstimate” - on a computed column? and that was solved, thanks Paul. Now I ...
8
votes
2answers
4k views

What is causing high CPU usage from this query/execution plan?

I have an Azure SQL Database that powers a .NET Core API app. Browsing the performance overview reports in the Azure Portal suggests that the majority of the load (DTU usage) on my database server is ...
76
votes
4answers
24k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
0
votes
0answers
48 views

Any way to optimize this query? Indexing? Re-structuring?

I'm having real issues with the performance of this query in my application: SELECT DISTINCT ON (pole_prices.station_id, pole_prices.fuel_grade_id) pole_prices.* FROM "pole_prices" ...
5
votes
1answer
2k views

Do foreign keys affect performance in Oracle?

Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases? One specific area i was ...
2
votes
2answers
1k views

UNION ALL implicit conversion for VARCHAR(MAX) column

Is an implicit conversion in a UNION ALL when one of the data type is VARCHAR(MAX) something to worry about? e.g. DECLARE @value1 VARCHAR(1)= 'a'; DECLARE @value2 VARCHAR(MAX)= 'a'; SELECT @value1 ...
0
votes
1answer
467 views

Plan changes to include Eager Spool causes the query to run slower

We have a reporting query which is erratic in terms of execution plan and run duration. It is either 5 seconds or as slow as 5 minutes. The query is a Select statement with no DML involved. One thing ...
1
vote
1answer
624 views

sql server query execution plan via jdbc driver

is there a way to fetch the XML generated by the "Actual Execution Plan" in SQL server 2012 - 2014, but via JDBC driver for Java -- sqljdbc_6.0.8112.100_enu? (preferable forward compatible with 2016) ...
0
votes
1answer
43 views

Query performance drastically depends on the database in use

I'm on SQL-Server 12.0.5203.0 There is a query covering multiple databases. I think It is not useful to post the actual query (as it is rather complex and confidential, just think of a query like ...
1
vote
2answers
150 views

Why does a many to one merge join change the sort order of the data set?

I have a query that calculates row_Number(). The table has a clustered index on the same columns (and order) as the partitioning and ordering for the row number. When using a merge join (many to one),...
4
votes
1answer
83 views

Interchanging IN with EXISTS produces different result sets

I am attempting to update a query that utilizes the IN operator within a WHERE clause predicate with EXISTS to compare potential performance improvements and better understand what is happening behind ...
1
vote
1answer
137 views

How to achive a many to one merge join in Azure Data Warehouse

I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm ...
0
votes
0answers
33 views

Are clustered indexes in Azure data warehouse ever a good idea on large tables?

Azure data warehouse supports both clustered and non-clustered indexes in addition to columnstore (which is the default for any new table). I know that having a large clustering key is normally ...
7
votes
1answer
131 views

What is the StatementParameterizationType plan attribute?

I have noticed execution plans sometimes include a StatementParameterizationType attribute. What is this, what does it mean, and when does it appear?
7
votes
4answers
568 views

DATEADD not producing a SARGable expectation of an index seek

I have a basic [UserActivity] table that captures an ActivityTypeId per UserId and the ActivityDate at which the Activity occurred. I am writing a query/stored procedure that allows for input of the @...
1
vote
1answer
135 views

Analyzing SQL Server query plan [closed]

I ran the sp_BlitzCache script from the Brent Ozar toolkit on a server that I am monitoring (I am new at the company for about 3 weeks). The result of the procedure has the following values: ╔════════...
1
vote
2answers
1k views

Clustered index update, how changing the index only at different column value?

I've table, example as Id int primary key(clustered index) Name varchar(255)not null (non-clustered index) .. .. Other columns I execute stored procedur as update table1 set table1.Name=isnull(@...
0
votes
1answer
339 views

Large cost change in execution-plan why?

I had a query change execution plans for an unknown reason. The stats were gathered about 24 hours before the change and the rate of data increase has not changed over the period in question. Here is ...
4
votes
1answer
782 views

Table with clustered index implicitly sorting by unique nonclustered index

I have a table that captures the host platform that a user is running on. The table's definition is straightforward: IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL BEGIN CREATE TABLE [...
2
votes
1answer
61 views

View columns elimination

I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one. Here is my view: CREATE VIEW Schema1.Object1 AS ...
2
votes
1answer
880 views

Estimated Rows Higher than Actual Despite Updating Statistics

I have a (anonymised) query plan There is a table (Object8) which has a high variation on the number of Actual Rows vs Estimated Rows on two columns (Column2 and Column6) which can be seen in the ...