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.

90 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
1answer
82 views

Why do we have Top N Sort in this example?

In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top? use tempdb; go create table dbo.t1 (id int identity primary key, v int); ...
4
votes
1answer
534 views

SQL query fills up RDS free storage space after upgrade to MySQL 5.7

After the upgrade to MySQL 5.7 the execution of a SQL query fills up the free storage space. The upgrade was performed through the AWS console, selecting the automatic procedure to upgrade the RDS ...
3
votes
0answers
111 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 ...
3
votes
0answers
107 views

Bad Plan after a couple of days

I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish. If I rebuild the plan, it flies. There is a signifigant amount of data ...
3
votes
0answers
420 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
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 ( ...
3
votes
2answers
107 views

Understanding Execution Plan

I am really having hard time understanding the below execution plan. Query 1: (Simple Select Query) Record count of ##SALES_UNIT_RESULT is 168. In first execution plan it is fine. ;WITH ...
2
votes
0answers
33 views

Advantage of querying data outside a filtered index

Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index? My playground: Table with billion rows and 100 columns Unique clustered ...
2
votes
0answers
27 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
2
votes
0answers
48 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 ...
2
votes
0answers
464 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-...
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"."...
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 ...
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 (...
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 ...
2
votes
0answers
216 views

Poor SQL query optimization of Recursive CTE with iTVF, environment specific

I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected. In our production environment, the first iteration of the CTE ...
2
votes
2answers
245 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
2
votes
0answers
195 views

Searching between two BIGINT

I have a table defined as: CREATE TABLE [dbo].[IPS_INTEGERS]( [id] [int] IDENTITY(1,1) NOT NULL, [network_start_integer] [bigint] NULL, [network_last_integer] [bigint] NULL, [...
2
votes
0answers
61 views

What can I do to determine why my query occasionally hangs (currently hung for over 3 days)

I have a query in my data warehouse load that occasionally hangs. Traditionally, it takes only a few minutes to run, however sometimes it can take a few hours. I'm unsure whether this is the query ...
2
votes
0answers
158 views

SQL Query produced by Umbraco app & NHibernate ORM - help!

https://www.brentozar.com/pastetheplan/?id=SJ1BhSun This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2. This query runs ...
2
votes
0answers
226 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
2
votes
0answers
300 views

Execution plan of query inside pl/pgsql function

Briefly, we have a pl/pgsql function which collects some intermediate data in several temporary tables ("on commit drop") and then returns REFCURSOR which joins those temporary tables with some static ...
2
votes
0answers
307 views

Does Oracle ADO.NET varchar parameter size influence query plan reuse?

I am considering to apply a fix which will generate constant parameter sizes for (n)varchar parameters. The application is based on NHibernate with the old OracleClientDriver (using Microsoft Oracle ...
2
votes
0answers
351 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
1
vote
0answers
15 views

Understanding PostgreSQL Execution Plans

I'm trying to understand the motivation of PostgreSQL 12 query planner here. Database Structure CREATE TABLE subjects ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ); CREATE TABLE ...
1
vote
1answer
241 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
1
vote
0answers
20 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 ...
1
vote
0answers
142 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 ...
1
vote
0answers
73 views

Is there a way to make postgres count planned number of rows when using recurcive CTE?

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle: create table test_recursive ( id serial primary key, parent_id int null references test_recursive, name text ...
1
vote
1answer
139 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
0answers
84 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 ...
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 ...
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, ...
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 ...
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 ...
1
vote
1answer
31 views

How to index cube style queries with changing aggrigations

I've got a query that emulates the CUBE function. This is many different aggregations unioned together. select x,y,z, sum(a) from tbl group by x,y,z union all select x,'all',z, sum(a) from tbl group ...
1
vote
0answers
177 views

why the queries run by a specific user are slow?

Here you can see how to save the contents of the default trace into a table. I have been saving the contents of the default trance into a table in my DBA database. The table is called [DBA].[dbo].[...
1
vote
0answers
57 views

Execution plan using index that no longer exist

I have some odd behavior with an execution plan using an index that no longer exists. let me give you the background. We had some high CPU going on so I had a look what was happening and found a ...
1
vote
0answers
872 views

Different execution plan for the same query on the same Oracle 12cR2 schema

I'm getting different execution plans for the same query running on the same Oracle 12cR2 instance. The query uses only literals (no variable binding) and takes about 10 seconds when I run it from my ...
1
vote
0answers
46 views

Measuring 'data items accessed' by index technique to retrieve item (PostgreSQL)?

In the example I have attached, a btree is being used to search for the number 19. With PostgreSQL execution plan, buffers read/hit are being shown. Which would they be in the example I have attached? ...
1
vote
0answers
464 views

Why is the Postgresql planner choosing the slower merge join (instead of hash join)

I'm using Postgres 9.6 on AWS I've run into an issue where depending on the data range for the query the planner will chose to do merge joins (requiring slower sort operations). When the date ...
1
vote
1answer
495 views

Window aggregate resulting in slow query on postgres 9.6

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query: explain analyze SELECT * FROM ( SELECT A.person_id, O.session_id, timestamp_ AS ...
1
vote
1answer
381 views

What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached ...
1
vote
1answer
93 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
1
vote
0answers
72 views

Using TempDB in SP causes to compile plan PER CONNECTION

uses sp_blitzcache by Brent Ozar SQL SERVER 2012 (11.0.6020.0) Steps to reproduce: create table b (b int) create table #b (b int) create procedure Y as begin insert into b select 1 select * from b ...
1
vote
0answers
42 views

Index added to view affecting other view

I created two views: vTest1 vTest2 The create procedure for each is: CREATE VIEW [dbo].[vTest1] WITH SCHEMABINDING AS SELECT Person.EmailAddress.EmailAddress, Person.Person.Title, Person....
1
vote
0answers
138 views

Logical read when use Offset in SQL

When I execute this script SET STATISTICS IO ON SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO] FROM vwTblTest WHERE ([Factor] >= 139501 AND [Factor] < ...
1
vote
1answer
65 views

How Plan Explorer Group Operators in Query Columns Tab

The two Index Seek (highlighted in green and red) are for filtering, they did not group in Query Columns tab. But the two Object3 highlighted in red are grouped together instead. Here is the XML Plan. ...