The strategy selected by the query optimiser to process a query.
3
votes
1answer
122 views
Optimizing complex join predicates [closed]
Table Definitions
CREATE TABLE [dbo].[Pin_Mtg] (
[MtgId] bigint NULL,
[CntyCd] char(5) NOT NULL,
[BatchDt] int NOT NULL,
[BatchSeq] int NOT NULL,
[MtgSeq] tinyint NOT NULL,
...
14
votes
4answers
440 views
Execution plan vs STATISTICS IO order
SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON?
The following query:
SET STATISTICS IO ON;
...
5
votes
2answers
200 views
How to optimise T-SQL query using Execution Plan
I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
1
vote
2answers
126 views
Updating a table efficiently using JOIN
I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
6
votes
0answers
109 views
Need help understanding query plan: estimates vs. actuals [closed]
I'd like to understand a section of the query plan that seems to mess up my query.
The picture shows that there's a clustered index seek, followed by compute scalar, followed by sort.
The part ...
6
votes
2answers
117 views
Query Plans not retained insufficient memory errors
We have been experiencing memory issues with SQL Server.
We first realised we had a problem when we started getting timeouts and login errors:
A connection was successfully established with the ...
2
votes
0answers
49 views
Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible
We are using Oracle 11g, 11.2.0.3.
We know a record exists in a table but a select is not returning it for some odd reason.
The execution plans the general query, not forcing any index, shows that ...
1
vote
1answer
84 views
Relative cost of two similar queries involving XML columns
I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.
I checked the execution ...
2
votes
2answers
46 views
On which server does a query execute?
Given two Microsoft SQL servers:
Say ServerA has a view titled "View1":
SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3)
and a stored procedure "sp_Foo":
SELECT * FROM View1 WHERE ...
6
votes
1answer
171 views
Why does the plan cache contain estimated and not actual execution plans?
I can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.
2
votes
1answer
78 views
Execution Plan not matching Stored Proc
Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists.
The execution plan shows an INNER JOIN of the M and A tables, where in the ...
7
votes
4answers
730 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
6
votes
3answers
239 views
Are SQL Server statistics stored in database or buffer pool?
Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that ...
3
votes
1answer
120 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 ...
4
votes
1answer
50 views
Are database query optimizers aware of storage performance differences?
As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has ...
2
votes
4answers
184 views
How can I remove a bad execution plan from SQL Azure?
DBCC FREEPROCCACHE doesn't work in SQL Azure. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? ...
5
votes
1answer
92 views
Are the cost percentages in this SQL Server plan over 100% for a valid reason?
I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet:
Why are many of the costs listed above 100% ? Shouldn't that be impossible?
4
votes
1answer
763 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 ...
2
votes
1answer
120 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 ...
8
votes
1answer
182 views
Should I be alarmed by this NO JOIN PREDICATE warning?
I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning
select
method =
case ...
14
votes
3answers
2k views
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
4
votes
2answers
70 views
Performance of different precedence pseudocolumn solutions with LIMIT
I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.
The OP wanted to get the first matching row given ...
3
votes
1answer
205 views
Execution plan in simple query in PgSQL depends on offset clause, why?
Update on 2013-01-16 11:08: see below
I am working on PostgreSQL 8.4.14 with the following table fed with 53k lines:
CREATE TABLE botany.taxon_data_cache (
taxon_id int NOT NULL,
...
4
votes
2answers
181 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 ...
14
votes
2answers
825 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 ...
2
votes
1answer
189 views
Non-clustered Index Update not displayed in execution plan [duplicate]
Possible Duplicate:
Nonclustered Index Insert
I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
8
votes
3answers
339 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 ...
9
votes
1answer
389 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 ...
9
votes
1answer
320 views
Does SQL Server 2008 store the creation date of execution plans?
We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
3
votes
1answer
134 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 ...
1
vote
1answer
92 views
Binding errors with dependent stored procedures
We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
4
votes
3answers
578 views
Large set of execution plans hangs up SSMS
I have a stored procedure that takes about 3-5 seconds that I'm trying to understand so I want an execution plan for it. When I run it in SQL Server Management Studio with Execution Plan enabled, it ...
2
votes
1answer
162 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 ...
0
votes
1answer
259 views
Query performance and join hints, plan cost, and duration
Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases.
Background
I have a reporting database using ...
9
votes
2answers
1k views
How (and why) does TOP impact an execution plan?
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
2
votes
1answer
178 views
Does a change in a query executed with sp_executesql cause recompilations?
I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure.
...
5
votes
2answers
236 views
Correlated subquery and join: still the same exectution plan?
I have a correlated subquery like this (from BOL):
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = ...
2
votes
3answers
796 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. ...
2
votes
1answer
141 views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
3
votes
1answer
97 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 ...
15
votes
2answers
616 views
T-SQL query using completely different plan depending on number of rows I'm updating
I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
4
votes
1answer
352 views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
...
1
vote
1answer
114 views
SQl code performance
What measurement do you know if this sql code is efficient based on execution plans?
How do you know if this sql code is efficient based on execution plans?
SELECT [CustomerID], ...
13
votes
1answer
679 views
Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
The three SELECT statements in this code
USE [tempdb];
GO
SET NOCOUNT ON;
CREATE TABLE dbo.persist_test (
id INT NOT NULL
, id5 AS (id * 5)
, id5p AS (id ...
2
votes
1answer
543 views
Is it correct, order of where clause doesn't matter when it is used with join?
I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.
I tried options like disabling rules and checked actual query ...
4
votes
2answers
662 views
How do I get the execution plan for a view?
I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used.
How do I do this?
I'd rather not have to copy and paste ...
4
votes
2answers
269 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 ...
13
votes
3answers
1k views
Can you explain this execution plan?
I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
7
votes
1answer
868 views
Interpreting SQL Server's Showplan XML
I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
2
votes
1answer
225 views
Query tuning - SQL Server
One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
