Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
12
votes
3 answers

SQL poor stored procedure execution plan performance - parameter sniffing

I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in: CREATE PROCEDURE MyProc @MyDate DATETIME = NULL AS IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP -- Do Something…
Justin
  • 84,773
  • 49
  • 224
  • 367
11
votes
3 answers

Why are the performances of these 2 queries so different?

I have a stored proc that searches for products (250,000 rows) using a full text index. The stored proc takes a parameter that is the full text search condition. This parameter can be null, so I added a null check and the query suddenly started…
Xavier Poinas
  • 19,377
  • 14
  • 63
  • 95
11
votes
2 answers

Redundant data in update statements

Hibernate generates UPDATE statements, which include all columns, regardless of whether I'm changing the value in that columns, eg: tx.begin(); Item i = em.find(Item.class, 12345); i.setA("a-value"); tx.commit(); issues this UPDATE…
Tair
  • 3,779
  • 2
  • 20
  • 33
11
votes
3 answers

Does altering a stored procedure expire cached execution plans?

Does executing a ALTER PROCEDURE statement for a stored procedure cause all cached execution plans for that stored procedure to become invalid and expire in SQL Server 2008 / 2005?
Kane
  • 16,471
  • 11
  • 61
  • 86
11
votes
1 answer

How to get the execution plan using LINQ to SQL/ADO.NET

Is it possible to get the execution plan of a LINQ to SQL or ADO.NET Query programatically for displaying in debug information? If so, how?
Chad Moran
  • 12,834
  • 2
  • 50
  • 72
11
votes
2 answers

Query with ORDER BY is 13 times as slow when I add LIMIT 1

I have this query (in postgresql): SELECT "table_1".* FROM "table_1" INNER JOIN "join_table" ON "table_1"."id" = "join_table"."table_1_id" WHERE "join_table"."table_2_id" = 650727 ORDER BY table_1.created_at DESC LIMIT 1 Which returns 1 result,…
nzifnab
  • 15,876
  • 3
  • 50
  • 65
11
votes
2 answers

Physical operators in SQL Server execution plans: what are rebinds, rewinds and number of executions?

I'm trying to understand physical operators in SQL Server execution plans. This page is quite helpful: http://technet.microsoft.com/en-us/library/ms191158.aspx SSMS 2008 shows some physical operator properties that are not displayed in SSMS 2005:…
Ben Challenor
  • 3,365
  • 1
  • 35
  • 35
11
votes
1 answer

What is an automatic covering index?

When using EXPLAIN QUERY PLAN in SQLite 3 it sometimes gives me output such as SEARCH TABLE staff AS s USING AUTOMATIC COVERING INDEX (is_freelancer=? AND sap=?) (~6 rows) Where does the index come from and what does it do? The table has no…
Tamlyn
  • 22,122
  • 12
  • 111
  • 127
11
votes
2 answers

Query executes slower after indexes are created and dbms_stats compute is used

I have a table with 1.5 million rows. I run a query which fetches records having non repeating values in a column. I am observing a behaviour in which after creating indexes the performance of the query degrades. I also used dbms_stats with 100%…
rirhs
  • 297
  • 2
  • 4
  • 9
10
votes
1 answer

Why does vacuum analyze change query plan while analyze does not?

I wanted to utilize the power of index-only scans in Postgres and experimented with one table: CREATE TABLE dest.contexts ( id integer NOT NULL, phrase_id integer NOT NULL, lang character varying(5) NOT NULL, ranking_value double precision, …
greatvovan
  • 2,439
  • 23
  • 43
10
votes
1 answer

Explain Vs Desc anomalies in mysql

What are the differences between EXPLAIN and DESC commands in MySQL ?
Puru
  • 8,913
  • 26
  • 70
  • 91
10
votes
1 answer

Programatically read SQL Server's query plan suggested indexes for a specific execution of SQL?

If I run this command in SSMS: set showplan_xml on GO exec some_procedure 'arg1', 'arg2','arg3' GO set showplan_xml off GO I get XML output of the full call stack involved in the query execution, as well as any suggested indexes etc. How might one…
tbone
  • 5,715
  • 20
  • 87
  • 134
10
votes
2 answers

Explain MySQL explain execution plan maths, difference between two plans

I've got a basic MySQL performance question related to explain. I have two queries that return the same result and I am trying to understand how to make sense of the EXPLAIN of the execution plans. The table has 50000 records in it and I am…
Stuart Wakefield
  • 6,294
  • 24
  • 35
9
votes
2 answers

SQL Server execution plans: filter to show bottlenecks?

Is there a way to only show items that are greater than 1% of the batch? I am trying to find bottlenecks in a proc, it contains loops and other logic and 99% of the resulting executing plan I don't care about, but it's hard to scroll through in…
CaffGeek
  • 21,856
  • 17
  • 100
  • 184
9
votes
2 answers

Same query - different execution plans

SQL 2008. I have a test table: create table Sale ( SaleId int identity(1, 1) constraint PK_Sale primary key, Test1 varchar(10) null, RowVersion rowversion not null constraint UQ_Sale_RowVersion unique ) I populate it…
Alex Aza
  • 76,499
  • 26
  • 155
  • 134
1 2 3
…
95 96