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

learn more… | top users | synonyms (1)

6
votes
3answers
97 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
3
votes
1answer
51 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
5
votes
3answers
137 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
3
votes
1answer
60 views

Finding hash match aggregate

After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate. Blocking/non-blocking aggregate operators The ...
6
votes
3answers
213 views

Execution Plan is NOT using INDEX and uses Table Scan

I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better. I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this ...
4
votes
1answer
122 views

how to ignore the SSMS (sql server management studio) sql server ansi settings?

The options in Management Studio On Management studio Tools->Options->Query Execution->SQL Server there are options that are set and these prevail over the same options that are set on database ...
14
votes
1answer
469 views

Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table: CREATE TABLE [dbo].[Accounts] ( [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), -- WHATEVER other columns ); GO CREATE UNIQUE CLUSTERED INDEX [AccountsIndex] ...
1
vote
2answers
102 views

OrderBy int slow for large table join

I have roughly 1.5 million records. This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling. I ...
4
votes
1answer
130 views

Actual Number of rows is too high even with new tables

I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high ...
6
votes
2answers
105 views

how to get estimated subtree cost?

If I have a query that returns a query_plan, for instance like this: SELECT TOP 1000 st.TEXT ,cp.size_in_bytes ,cp.plan_handle ,QP.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS ...
-1
votes
1answer
65 views

Why switch execution plan for consecutive, identical queries? [closed]

This question is not about 'What should be'. The situation is the situation. I'm just seeking understanding (at this point). Select statement specifies 13 fields to return but only provides five ...
1
vote
1answer
54 views

Incorrect statistics are used on index seek

I have a table-valued function and ordinary table and need to perform INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in ...
2
votes
2answers
57 views

SQL Server - SQL Plan Management for baseline

I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after ...
6
votes
1answer
128 views

Set statistics I/O for nested loops

Consider the below query: CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key @pagesize AS BIGINT = 25 AS SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM ...
1
vote
2answers
86 views

Why query plan changes by number of rows

I execute following two queries on an oracle database: select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507 which returns result in less than a second and with following ...
7
votes
1answer
316 views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
0
votes
0answers
41 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
6
votes
2answers
293 views

SQL Server changes execution plan - Part 2

Previous question: SQL Server changes execution plan We are using SQL Server 2014 Developer Edition. SQL Server changes execution plan on identical query and the same database and SQL Server (I ...
2
votes
2answers
105 views

SQL Server changes execution plan

I have SQL code that is executing slow DECLARE @ccode varchar(500) SET @ccode = '%NIP%' SELECT [CVN_UID] FROM [Klasje].[dbo].[VERSION_CATEGORY_XREFS] WHERE DESCRIPTOR_TEXT LIKE @ccode Why is SQL ...
1
vote
1answer
54 views

Forcing correct execution plan when passing a table UDT to a table function

I have a scalar function that returns a big XML, created over a bunch of invoices. The exact list of invoices to feed to the function can be calculated using several different ways, but the function ...
1
vote
1answer
20 views

execution plan with and clause in where

I have this SQL statement: select * from _my_table1 where 1 = 0 and (exists (select 1 from _my_table2 where id =7)) The execution plan is: |--Constant Scan Now when I change my query to ...
1
vote
3answers
76 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
4
votes
1answer
53 views

Optimizing “random” choice in PostgreSQL

I have the following table CREATE TABLE article ( id bigserial PRIMARY KEY, text TEXT NOT NULL, category_id bigint REFERENCES categories (id), status TEXT NOT NULL, locale TEXT ...
2
votes
2answers
64 views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
11
votes
1answer
305 views

Execution plan with multiple missing indexes

If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the ...
3
votes
1answer
58 views

Query plan missing ParameterCompiledValue

Recently I've started to analyze execution plans. Today I stumbled on an execution plan that has no ParameterCompiledValue (and no ParameterList tag). It is a plan of a select with one inner join and ...
0
votes
1answer
37 views

List of nodes types in a query plan in PostgreSQL?

Where can I find an exhaustive list of possible nodes types(e.g. Seq scan, groupAggregate, Nested loop ...) that a query plan could contain in PostgreSQL?
3
votes
2answers
179 views

Measure compilation time of stored procedure

I need to measure how much time it takes for the MSSQL-2012 to compile a stored procedure. Is it possible?
1
vote
0answers
34 views

Greatest N per Group in SQL Server [duplicate]

I want to find classical Greatest N per Group. I have two methods to solve this problem Dense_rank Over() method Min Over() method Both work flawlessly. Now I want to find which one is better ...
6
votes
2answers
93 views

Fixed query plan for a stored procedure

I am exploring the plan guides topic. Sounds easy, as far as we go through the examples in documentation. The problem is that I cannot find any working example (nor did I manage to figure it out by ...
2
votes
1answer
114 views

SQL Server 'ignores' drop table command [duplicate]

I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the ...
1
vote
1answer
87 views

How can I extract a list of dependent tables from an execution plan?

Is there a way to extract a full list of tables and views used in a query? I need to replicate a portion of a database so that we can avoid locking tables when a long query runs. I have the ...
10
votes
2answers
188 views

What is the sense and benefit of using SqlCommand.Prepare()?

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this? Sample: ...
0
votes
1answer
36 views

Execution plan not understanding correctly

I am running a query: SELECT * FROM message where fr_user = 1 ORDER BY created DESC LIMIT 10; Without the LIMIT there would be 170 rows returned where fr_user = 1; I am now trying to understand ...
3
votes
1answer
69 views

Having hard time understanding expressions in below query plan

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause which returned below query plan Rows Executes StmtText StmtId NodeId ...
4
votes
2answers
96 views

Optimized date compare in WHERE clause, convert or datediff=0?

In my query I am comparing two dates in the WHERE clause once using CONVERT: CONVERT(day,InsertedOn) = CONVERT(day,GETDATE()) and another using DATEDIFF: DATEDIFF(day,InsertedOn,GETDATE()) = 0 ...
1
vote
0answers
44 views

Why does the first query scan and the second seek?

Please could somebody explain why the first query below scans on every JOIN to the REFERENCE_VALUES table but the second query seeks on the same JOINs. The first query does have other JOINs but I ...
4
votes
1answer
138 views

PostgreSQL seems to create inefficient plans in simple conditional joins

Consider these two queries: SELECT t1.id, * FROM t1 INNER JOIN t2 ON t1.id = t2.id where t1.id > -9223372036513411363; And: SELECT t1.id, * FROM t1 INNER JOIN t2 ON ...
3
votes
1answer
83 views

Aggregation GROUP BY bottleneck

I have a performance bottleneck with a SELECT GROUP BY operation. Schema I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT ...
6
votes
1answer
146 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t ...
4
votes
1answer
76 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
9
votes
1answer
433 views

Where does an execution plan come from?

Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
5
votes
2answers
95 views

Is execution plan cached “better” for stored procedures than for a non-dynamic query?

Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries. By a non-dynamic query ...
4
votes
2answers
112 views

Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index

On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads ...
2
votes
1answer
28 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
0
votes
1answer
29 views

Can I use the cost of a query to prove its efficiency when there is only a small number of rows?

Can I run EXPLAIN (Oracle) for a query when there is only a few dozen rows in the table and use the cost to prove whether the query is efficient or not ? Can you measure the efficiency of a query ...
0
votes
0answers
53 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
2
votes
2answers
248 views

Can I disable execution plan caching for debugging purposes?

I am trying to isolate poor performing queries in our application. While query execution plan caching is great in production it is interfering with our debugging efforts. Is there are way to ...
0
votes
2answers
142 views

Force Sql server to use index seek in merge join

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN: ID in Personal Table is a Primary key ID in Abteilung Table is a primary key There is no other ...
0
votes
1answer
189 views

Simple select query taking too long

I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this ...