The strategy selected by the query optimiser to process a query.
6
votes
3answers
146 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
77 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
39 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
3answers
90 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)? ...
4
votes
1answer
62 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
407 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
64 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
153 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 ...
10
votes
2answers
734 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 ...
3
votes
2answers
61 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
134 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
176 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 ...
13
votes
2answers
550 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 ...
1
vote
1answer
138 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 ...
7
votes
3answers
277 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 ...
8
votes
1answer
310 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
196 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
109 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
79 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 ...
3
votes
3answers
434 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
123 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
220 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 ...
8
votes
2answers
885 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
138 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
227 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
514 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
113 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
95 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
504 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
265 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
106 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], ...
10
votes
1answer
470 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
512 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
515 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
240 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 ...
12
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
707 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
215 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 ...
1
vote
2answers
116 views
Execution Plans Query
I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard ...
4
votes
1answer
110 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 ...
3
votes
1answer
100 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
1answer
489 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 ...
4
votes
1answer
676 views
Why does it take so long to calculate an Execution Plan?
One of our customers has just upgraded to a new server.
For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.
...
1
vote
2answers
391 views
In DB2, how can I disable NLJOIN?
I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query.
I've been looking for this for a while without any success. I ...
1
vote
1answer
525 views
SQL Server 2008 R2 sys.dm_exec_sql_text question
If i run the following query on our production server:
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time / 100000 AS MaxElapsedTime,
...
8
votes
1answer
1k views
Are there any risks to granting users SQL Server SHOWPLAN permission?
I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
5
votes
1answer
369 views
Execution plan using B+ tree index, but also sorts
I'm using Oracle sqlplus. And I have the following query:
SELECT fooID from foo MINUS
SELECT fooID from bar;
I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
1
vote
3answers
874 views
Why would recompile query hint result in different plan for same adhoc statement after freeproccache?
This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.
I've got an adhoc sql statement that gets a different query plan solely because of ...
2
votes
2answers
672 views
Why is query using Clustered Index when it shouldn't?
Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
6
votes
2answers
302 views
Code creates different plan when ran ad-hoc vs. in a stored procedure
I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.
I have rebuilt all the indexes for the tables used by the ...
