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
5
votes
4answers
338 views
+150

SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

I have a very simple query: INSERT INTO #tmptbl SELECT TOP 50 CommentID --this is primary key FROM Comments WITH(NOLOCK) WHERE UserID=@UserID ORDER BY CommentID DESC against this table: CREATE ...
15
votes
3answers
3k views

Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
14
votes
1answer
2k views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
8
votes
2answers
235 views

Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators?

When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan. To reproduce this,...
11
votes
1answer
2k views

Estimated versus actual query plan with function calls

I have this query on SQL server, a merge replication query: SELECT DISTINCT b.tablenick, b.rowguid, c.generation, sys.fn_MSgeneration_downloadonly ( c.generation, ...
6
votes
1answer
88 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); ...
0
votes
0answers
21 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
0
votes
1answer
20 views

How do I make Postgres use an index when using IN or ANY?

I have a query such as: select col1 from mytable where col1 in('a','b','c') But running such a query yields a Seq Scan per EXPLAIN If I have an index on col1, why does Postgres not do an index scan?...
2
votes
1answer
27 views

How to get more physical details when executing an SQL query?

I want to get some physical features about a SQL query execution with PostgreSQL, like Read/write page accesses I/O operations Response time Sequential I/O throughput rate Deadlocks Pages scanned ...
34
votes
2answers
13k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[...
6
votes
1answer
90 views

When are non-parameterized, non-trivial, adhoc query plans reused

I'm currently investigating an application that seems to generate 99% adhoc query plans against the database it is querying. I can verify this by running the following statement to retrieve a summary ...
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 ...
9
votes
1answer
143 views

Is it possible to re-use a query plan from the plan cache from one database in another database?

For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...
7
votes
1answer
468 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
0
votes
1answer
92 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
1
vote
1answer
37 views

Query plan for a dynamically built `query_int` using `format()`

To make things simple here is a statically built version of the query that runs perfectly fast: SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20; The catch ...
2
votes
1answer
178 views

Ideas how to make this query faster?

I have the following query: SELECT TabPohybyZbozi.ID, TabPohybyZbozi.NazevSozNa2, TabZakazka.Nazev, TabDokladyZbozi.DatPorizeni, TabPohybyZbozi.IDDoklad FROM ...
1
vote
1answer
35 views

How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time

I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory ...
9
votes
2answers
491 views

Xml index, slow count of rows

I am not getting index seek in the following scenario. Instead of inserting <Number>xxx</Number> in xml column like in this post Why is the secondary selective index not used when the ...
1
vote
2answers
127 views

Why won't SQL Server 2012 use my “better” execution plan when provided via the USE PLAN hint or through a stored query plan?

I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done ...
0
votes
1answer
203 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
13
votes
1answer
532 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
0
votes
1answer
47 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 ...
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"."...
5
votes
3answers
1k views

looking for missing indexes in the cached execution plans

I have the following query that shows me what is cached in sql server: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS ...
1
vote
3answers
88 views

Filtered index is not used when variable in WHERE condition

Why MS SQL Server refuse using supporting filtered index in this scenario? -- demo data CREATE TABLE #Test ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY ,Col1 NVARCHAR(36) ...
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 ...
1
vote
2answers
82 views

How to check actual execution plan of a query after it has been executed

In SQL Server, how can we see actual execution plan of a query, after it has been executed? As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is ...
2
votes
2answers
246 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)...
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 ...
2
votes
1answer
39 views

Improving the query standards and performance run time

For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i ...
0
votes
2answers
119 views

Horribly performing query, how to fix

select user_id, count(id) as unread_count from notifications where is_read = false and user_id in(select(unnest('{200 user IDs}' :: bigint[]))) group by user_id; Problem is, ...
2
votes
1answer
95 views

How to rewrite CASE expression to short-circuit evaluation

I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase? Demo data: CREATE TABLE #...
0
votes
1answer
69 views

Worse query performance even after improving the query by making it to run in parallel mode

I have a SQL Query which gets generated from application using EFF. It uses a view and this view has joins to multiple tables in there. The query runs slow and when i looked at the execution plan,i ...
0
votes
0answers
63 views

Explicit conversion to avoid the implicit conversion warning [duplicate]

I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below. WITH X AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus, ...
7
votes
2answers
4k views

Estimated Operator Cost Calculation

Everywhere I have read says that the Estimated Operator Cost is the sum of the Estimated CPU Cost and the and the Estimated I/O Cost. However, in many operators I see, this is not the case. Here is an ...
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 ...
9
votes
1answer
36k views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
15
votes
1answer
424 views

Which costing factors go into the optimizer choosing different types of spools?

Spoolum In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries. Read only queries, particularly on the inner ...
14
votes
2answers
913 views

Constant Scan spooling

I have a table with a few dozens of rows. Simplified setup is following CREATE TABLE #data ([Id] int, [Status] int); INSERT INTO #data VALUES (100, 1), (101, 2), (102, 3), (103, 2); And I have a ...
3
votes
1answer
503 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. ...
8
votes
2answers
363 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
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
votes
1answer
45 views

Query Plan for a linked-server query getting cleared from plan cache after every execution

I have linked server query which runs of a view created in the remote server. Whenever i run the query i can see that a Prepared plan gets created in the plancache. But that gets removed from the plan ...
0
votes
1answer
44 views

Query runs in serial and parallel mode in two identical databases on the same instance [duplicate]

I have two databases in the same SQL Server instance. One is a test database with a smaller number of records and total size of 500GB. Other one is production database with a size of 2.2 TB. Both ...
1
vote
2answers
255 views

Another bad execution plan!

This is running SQL Server 2012 SP2 Enterprise Edition. I have a nightly ETL job that, for a specific SP, is taking way too long to run. During my investigation I found that it was a specific update ...
1
vote
1answer
52 views

How to get EstimatedRows values from SQL Server execution plan nodes

When I execute the following code in an Azure SQL Database (v12) or SQL Server 2016 instance, I get values in the EstimatedRowsRead column, but not in the EstimatedRows column, and I'm not seeing why. ...
6
votes
1answer
110 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=@...
14
votes
1answer
398 views

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query ...
7
votes
1answer
161 views

Inserting with implicit type conversion causes warning for cardinality estimates

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning: Type ...