Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [execution-plan]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
35 views

Stored Proc Logical Reads Spiking Daily At The Same Time [closed]

I have a procedure that I optimized in a dev environment. It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds. I got it down to 96k reads and 8 seconds. ...
1
vote
0answers
55 views

Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked ...
0
votes
1answer
321 views

How to Replace Hash Match (Partial Aggregate) with Stream Aggregate

My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
0
votes
1answer
46 views

How to get information about query plans that are out of cache?

In SQL Server there are a lot of DMV/Fs and other options which allow me to get information about query plans that are in cache, but I can't find any method to get information about all query plans, ...
1
vote
0answers
55 views

SQL Server Execution Plan

In the following queries the Student table has Clustered Index on StudentID column, and the Enrollment Table also has a Clustered Index on EnrollmentID as well as Non-Clustered index on (SessionID, ...
4
votes
1answer
143 views

SQL Server High Hash Match and Sort

I have an SQL statement that is taking about 40 seconds to run and the only thing I see is an high hash match and sort. I have added indexs to the temp table but still taking an long time to complete. ...
2
votes
1answer
33 views

Reasons why batch generates more than one query plan?

I know that a single batch can have more than one query plan but want to learn some more about the cases when more than one execution plans are generated. Is it because of a batch length or the use of ...
1
vote
1answer
109 views

Postgresql bad planning with multicolumn gist and search with empty results

I'm having an issue with a multicolumn index (integer, geom), where geom are PostGIS points, and the integer is a point aggregator with business meaning. I do searches by boxes in the geom column, ...
6
votes
4answers
234 views

Why the query optimizer choose this bad execution plan?

We have a mariadb table (stories) with above 1TB of data, periodically running a query that fetches recently added rows for indexing somewhere else. innodb_version: 5.6.36-82.1 version : 10.1....
0
votes
1answer
54 views

Postgres query planner issues

There are two cases from different DBs and my main questions are: why it is happening? what can I do to stop it from happening? OK, case #1 EXPLAIN (ANALYZE, BUFFERS) SELECT "notes"."id" AS t0_r0,...
1
vote
1answer
233 views

what are memory fractions and how to get rid of them (or the sort operator) in the example below?

This execution plan has the following memory fractions: Memory fraction input:1, Memory Fraction Output:1. it is a very simple query: SELECT [a].[activityId], ...
0
votes
1answer
66 views

Different execution plans and joins using synonym or table

Edit: We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all)....
5
votes
2answers
879 views

SELECT is using a non-PK Index instead of the PK

In the [dbo].[Programs] table, the column [Id] is the PrimaryKey (not par of a composite key). There are also quite a few other indexes on that table. When I'm running this simple query, SELECT [Id] ...
0
votes
2answers
69 views

Need to understand the drastic difference in execution plan between two queries

I have 2 queries. select guid=NEWID() into #guid --update statistics #guid with all --create statistics s1 on #guid(guid) select * from Party a join #guid b on a.[Party_GUID] = b.guid --...
1
vote
1answer
205 views

MySQL Execution Plan Recompile

I've added a new index to account for some performance issues however the queries that I would expect to be using the new index are still are not using it. In SQL Server you can flush an execution ...
2
votes
1answer
57 views

Why does MySQL optimize query with “WHERE … OR” so badly?

Hey there, this is my first question so please be patient if I miss to add something in here. My question is why the OR in the WHERE clause causes the query to be executed the way it is (which is ...
2
votes
0answers
472 views

Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?

I run the following query against Postgres 9.5 which returns article information along with the currently valid price: WITH selected_article_rm(id) AS ( VALUES ('80512e52-a406-4a9b-a8df-...
7
votes
1answer
6k views

Warning about memory “Excessive Grant” in the query plan - how to find out what is causing it?

I am running a query that is giving the warning about a memory Excessive Grant. There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
7
votes
3answers
5k views

Why does my query run fast in Environment A, but slow in Environment B?

I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B! Environments are supposed to be the same, so what should I do and/or ...
2
votes
3answers
87 views

Very complicated query is suddenly taking 35 minutes to finish in SQL Server 2014

I have a very complicated query that produces correct results slowly but ok in SQL Server 2012. After upgrade to SQL Server 2014 and after setting compatibility mode to 2014 (120) for the database, it ...
6
votes
1answer
326 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 ...
10
votes
1answer
275 views

Why doesn't join elimination work with sys.query_store_plan?

The following is a simplification of a performance problem encountered with the Query Store: CREATE TABLE #tears ( plan_id bigint NOT NULL ); INSERT #tears (plan_id) VALUES (1); SELECT T....
6
votes
1answer
317 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 ...
0
votes
1answer
315 views

Why do two SQL Server instances give different plans for the same query

I executed the following locally in SSMS for each of two instances of SQL Server 2012 SP4-OD (11.0.7469.6) that live in separate Azure VMs and get very different execution plans: SET STATISTICS IO, ...
3
votes
1answer
418 views

Parameterized query creating many plans

I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016. Queries are like: (@P1 varchar(1043),@P2 varchar(6)) UPDATE table ...
4
votes
1answer
367 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
6
votes
1answer
172 views

Query is fast but becomes sluggish when created as a view

Execution Plan as Query SELECT VP.Branch , VP.ROUTE AS Route , VP.SAPCustomerID , S.CustomerID , S.ProductID , S.Date -- Group by Customer , CustomerQuantity = SUM(...
13
votes
3answers
365 views

Why is selecting all resulting columns of this query faster than selecting the one column I care about?

I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo. This is the query: select top 1000 c.ID from ATable a join ...
3
votes
1answer
400 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
1
vote
1answer
68 views

Finding Serial plan from Plan cache

My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for ...
3
votes
1answer
483 views

How to determine index needed to remove hash match from exec plan

Sql Server Database on Azure (Sql Server 2014/12.0.2000.8) I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed ...
0
votes
2answers
256 views

Sequential scan with large sub-query filter “never” ends

I'm using postgres 9.2.4, and run the following query: explain analyze select * from bubu where id not in (select bubu_id from kuku limit 33554431); ...
5
votes
1answer
478 views

How to parse query plans to get a count of nested loop operators with unordered prefetch?

My boss wants me to parse a set of query plans stored in a table and to determine how many nested loop operators with unordered prefetching are present in each plan. I only have around 100 query plans ...
2
votes
1answer
1k 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=...
1
vote
1answer
181 views

Sql Server Plan - what is a Filter after an Index operation?

I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL ...
4
votes
1answer
143 views

Problem understanding an execution plan

I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is: select P.PERSONID from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, ...
0
votes
1answer
65 views

SentryOne Plan Explorer properties window

Does the SentryOne Plan Explorer have a properties window similar to SSMS. I see the tool tip window next to the operators in the execution plan but no properties option ?
1
vote
1answer
562 views

Postgres bypassing index with IN clause with 100+ values?

I came across this on another forum: PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
2
votes
0answers
104 views

Plan cache mystery

I work on SQL Server 2016 and i had severe performance issues with few queries. So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
2
votes
1answer
89 views

SQL server slow query

I have the following query that is taking a long time to execute (nearly 20 seconds). SELECT Count(*) FROM "workorder" "wo" LEFT JOIN "workorder_fields" "wof" ON "wo"."...
3
votes
1answer
81 views

Multi-Column index not used for index-only scan, but partial index is

My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is. The table: CREATE TABLE test ( id INT, descr ...
2
votes
2answers
174 views

Difference in query execution plan between MySQL 5.5.54 and mariadb 5.5.56

I am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. ...
3
votes
1answer
142 views

How can I find out if a SQL Function can be inlined?

In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
1
vote
1answer
73 views

Is there any way to find the number of pages read by each operator in query plan?

I need to find number of pages scanned by the operator in a sql-server query plan. I used SET STATISTICS IO ON This returned number of logical, physical, read-ahead pages scanned per table, but I ...
0
votes
0answers
26 views

Increased rate of bad plans (Oracle ACS)

In the last weeks I noticed an increased number of cases where Oracle starts to pick notoriously bad plans for some statements on widely different systems (Exadata, ODA and AIX). I haven’t seen that ...
7
votes
1answer
76 views

Postgres: query planner not respecting table inheritance constraint when querying for null

I'm having an issue with slow queries that is caused by the planner checking every inherited table instead of only the one with the constraint. I have a table with 0 rows called "search_result". This ...
2
votes
0answers
79 views

Problem query - execution plan

I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
5
votes
2answers
194 views

Query Plan question

I've created a varbinary hash to check changes between 2 tables. Here is the execution plan, I'm a bit stumped on the indexing, or indeed if there is a better way of writing this. https://www....
0
votes
2answers
132 views

Query Plan - Estimated Row Count Very High

I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal. However, I then need to split the TaskName ...
9
votes
1answer
397 views

Index Seek Operator Cost

For the AdventureWorks sample database query below: SELECT P.ProductID, CA.TransactionID FROM Production.Product AS P CROSS APPLY ( SELECT TOP (1) TH.TransactionID FROM ...