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

learn more… | top users | synonyms (1)

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 ...
4
votes
2answers
946 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
4
votes
2answers
573 views

Oracle build_plan_xml visualizer

Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
2answers
619 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
4
votes
1answer
453 views

How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?

Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
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 ...
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 ...
4
votes
2answers
169 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...
4
votes
1answer
168 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
4
votes
2answers
229 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
4
votes
1answer
119 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
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 ...
4
votes
1answer
188 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
4
votes
1answer
534 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
4
votes
1answer
136 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
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 ...
4
votes
1answer
114 views

Should I use a subquery to help SQL Server find the correct plan

I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
4
votes
1answer
1k views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
4
votes
1answer
80 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
4
votes
2answers
252 views

SQL Server doesn't use expected index

I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL): SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP ...
4
votes
1answer
1k views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
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 ...
4
votes
1answer
1k views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
3
votes
3answers
505 views

Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan. The following are the ...
3
votes
2answers
97 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
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 ...
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?
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 ...
3
votes
2answers
936 views

Use a view to join tables or a stored procedure for a better execution plan?

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans. What I want to know is for a stored procedure which the ...
3
votes
2answers
159 views

Changing metadata changes query output issue [duplicate]

Recently stumbled across the following problem: changing metadata changes query output. Here is how to get it. CREATE TABLE [dbo].[prod] ( [ID] [tinyint] NOT NULL, [values] [tinyint] NOT NULL ) GO ...
3
votes
1answer
2k views

Clustered Index Update on Nonclustered Columns

Using: SQL Server 2008 R2 I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
3
votes
1answer
568 views

How to determine cause of runtime increase given two query plans with SpillToTempDb warning

Two actual query plans were captured for two executions of one query: Plan "Fast" took ~1s and occurs about 90% of the time. Plan "Slow" took ~16s. Since the graphical plans look ...
3
votes
1answer
575 views

sql server partitioned view execution plan questions

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table) datepart(year,[Date])=(2010) datepart(year,[Date])=(2011) ...
3
votes
2answers
502 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
1answer
2k views

How bad is query plan pollution (too many equivalent plans in cache)?

Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well. I noticed some old modules of a system have non-optimal SQL ...
3
votes
1answer
139 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
3
votes
2answers
219 views

Any SQL where clause performance similar to where 1=0

(i'm using SQL Server 2008R2 or later) For centralize manage and consistent, there is a view definition as follow: CREATE VIEW view_all_situation ( select null as src, tf.col1, sum(col2) as fee, ...
3
votes
1answer
45 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
3
votes
1answer
368 views

Trace Flag 4199

Microsoft's KB article about Trace Flag 4199 is a bit confusing: ...any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Except for fixes ...
3
votes
1answer
420 views

SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

I was running the following query in SSMS and it showed me a warning in the "Include Execution Plan" window select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED from ( select payment.CLIENT_GUID, ...
3
votes
1answer
2k views

Fast paginated result, for various filter clauses

I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I ...
3
votes
3answers
6k views

Revert to old plan in Oracle

A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday. One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes. ...
3
votes
1answer
48 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 ...
3
votes
1answer
120 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
3
votes
2answers
3k views

SQL Query too slow on SUM function

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is SELECT f.RetailerID , TypeID ...
3
votes
2answers
254 views

Query taking too much time

My query is taking too much time to execute. Please help me here. mysql> explain select pcm.catalog_id from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c ...
3
votes
1answer
652 views

How are foreign keys resolved when creating tables in batch?

I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
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 ...
3
votes
1answer
140 views

Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] ...
3
votes
1answer
194 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...