Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
0
votes
0answers
11 views
Compare PostgreSQL Execution Plans
PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
3
votes
0answers
39 views
What Triggers sp_recompile in database without triggers, stored procedures, etc?
In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly ...
0
votes
0answers
16 views
Difference between Materialization and Pipelining
I would like to know about the difference between Materialization and Pipelining with the algebra tree. And the individual advantage and disadvantage of them.
I did some reading and have a additional ...
0
votes
0answers
34 views
Same data, same actual execution plans, but runtime is 4x longer [on hold]
We have a query which is puzzling us and we wanted to post it on this StackExchange site for comment.
In preparation, we camouflaged our table names and column names by selecting the relevant column ...
2
votes
1answer
28 views
Why are there multiple plans for query under forced parameterization?
I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:
delete from [...
4
votes
1answer
79 views
High processor utilization when running a stored procedure
I have a stored procedure which was running fine till 12/11 with average execution time under 2 hours. From Tuesday (12/11) onward, the stored procedure is running continuously for more than 3 hours ...
1
vote
0answers
39 views
Plan compile time almost the same resulting in same plan creation, but no plan reuse
Issue:
Queries are being executed fast after eachother, but plan reuse is only happening for 1 or a few executions. Many of these queries and query plans are found on the server.
Result of this is ...
6
votes
1answer
274 views
Why would a Clustered Index Seek return a higher “Actual Number of Rows” than there are rows in the underlying table?
I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, ...
3
votes
1answer
169 views
Unique filtered index and surprising estimation
When I view the estimated execution plan for this query...
SELECT *
FROM tabela2 x
LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod
... the Estimated Number of Rows property for [table1]...
2
votes
0answers
30 views
Poor query planning in Postgres 10
The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.
Here's my ...
1
vote
1answer
32 views
MariaDB uses “wrong”/irrelevant Keys on large table
We have migrated our database from a local hosting company to AWS.
Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
1
vote
2answers
59 views
SQL Server Execution Plan on server level
Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I ...
0
votes
0answers
25 views
Getting Hash Match in query
We noticed recently that a production SQL server was creating a downstream application performance issue. We narrowed the issue down to a specific query:
SELECT ExistingSiteFlag = ' ', Invtid =...
2
votes
1answer
47 views
Efficient predictable ordering in PostgreSQL
I have a large table of locations. I would like to efficient paginate though the table. I had previously being using an OFFSET approach however the size of the table made that approach unusable. So I ...
1
vote
0answers
40 views
Converting an XML field to nvarchar causes warning on main SELECT in explain plan
I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following:
SELECT
ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''')
FROM myTable p
This ...
0
votes
0answers
26 views
when to use Simple or Forced Parameterization in my databases?
I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it.
it turned out ...
2
votes
1answer
46 views
Query processor could not produce a query plan because of the hints defined in this query
There is a similar question, but it is not the same:
The query processor could not produce a query plan
I have the following query, and the following filtered index, and I cannot see any reason why ...
2
votes
1answer
32 views
execution plan TABLE ACCESS BY INDEX ROWID cardinality
This is the explain plan for a certain query.
The query contains a PK order by clauses and matches on two other columns on the where clause.
Since PK is used for index full scan, I assume cardinality ...
0
votes
1answer
41 views
Costly HASH MATCH Aggregate
I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan
CREATE ...
6
votes
1answer
430 views
“Warnings: Operation caused residual I/O” versus key lookups
I've seen this warning in SQL Server 2017 execution plans:
Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.
...
2
votes
2answers
136 views
Force SQL Engine to prepare Non-Null plan only for a query that use variable
I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.
Approach 1
SELECT *,
SUBSTRING(IDAndCode,CHARINDEX ('$', ...
2
votes
1answer
68 views
Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries
Issue:
I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type.
...
0
votes
1answer
30 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
35 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
47 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
44 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
45 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
0answers
81 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
30 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
43 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
91 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
39 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
68 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
43 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
852 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
53 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
24 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
51 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
138 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-...
3
votes
1answer
627 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
2k 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
67 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
109 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 ...
9
votes
1answer
181 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....
5
votes
1answer
262 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
74 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, ...
2
votes
1answer
272 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
...
3
votes
1answer
112 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
155 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
300 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 ...
