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
0
votes
0 answers

SQL Server 2008 converts query parameters string value to nvarchar(4000)

I am trying to fetch data using composite primary key. I am sending 2 strings as query parameters, but SQL Server execution plan shows query parameters @p0 nvarchar(4000) & @p1 nvarchar(4000) and it is using index scan instead of index seek.…
user3382471
  • 11
  • 4
0
votes
0 answers

Oracle query returning 8 rows runs > 10x slower through asp.net than sqldeveloper

I have a GridView loaded by an SqlDataSource with the System.Data.OracleClient provider using a select command that returns 8 rows. When the page loads, the time between the Selecting and Selected events is 35 seconds. I've verified that the query…
nebulopathy
  • 101
  • 7
0
votes
1 answer

SQL Stored Procedure issue due to the Lack of SP Performance

The main problem is that in my sp there are different objects and logic that may cause lack of performance. The things that I see on my SP and the tables that are in use in the SP 1- Temp tables such as shown below; (2 Temp Tables) CREATE TABLE…
Bilgin Kılıç
  • 8,707
  • 14
  • 41
  • 67
0
votes
1 answer

table scan vs Clustered index scan

I have a table, which in most of the cases, I need to fetch all the rows. It have 10334 records in it now, not expected to grow beyond 15K. The table is frequently updated. I can create a Clustered index if needed. But will it be a good idea,…
Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
0
votes
0 answers

SSMS2012 does not show non-index predicates on index seek?

Although SSMS2012 execution plan does show me all the seek predicates of a non-clustered index seek operation, it does not show any other predicates: However, if I save the execution plan as XML file, I can see other predicates: …
D.R.
  • 20,268
  • 21
  • 102
  • 205
0
votes
2 answers

SELECT DISTINCT With WHERE Clause is VERY SLOW vs. SELECT DISTINCT with INNER JOIN AND Clause

We have a production stored procedure that executes in 2-3 seconds everywhere except one client's environment. Their environment appears healthy with 24 cores, 64 GB RAM and is nowhere near capacity. The SQL Server is 2008 r2 SP2. I have restored…
unhappyCrackers1
  • 813
  • 8
  • 15
0
votes
1 answer

SQL Execution plan changes during the day

I have a query that never finishes (takes more that 2 hours) when I run it at 11am, and takes 1 min when I run it at 7pm. So obviously the execution plan changes during the day. What can cause that? The data does not change. Auto stats are turned…
IgorShch
  • 149
  • 1
  • 4
  • 21
0
votes
1 answer

Query creates different execution plan from app server than toad

Just to preface, I've seen a dozen similar SO Q/As and they appear to be a bit different (different server, different parameters etc). Basic problem Here's my question, we're running a select statement from the web server against our exadata 11g…
Kingpin2k
  • 47,277
  • 10
  • 78
  • 96
0
votes
1 answer

Indexing to reduce cost of SORT

I have this table: TopScores Username char(255) Score int DateAdded datetime2 which will have a lot of rows. I run the following query (code for a stored procedure) against it to get the top 5 high scorers, and the score for a particular Username…
Knightwisp
  • 315
  • 1
  • 13
0
votes
3 answers

Awkward JOIN causes poor performance

I have a stored procedure that combines data from several tables via UNION ALL. If the parameters passed in to the stored procedure don't apply to a particular table, I attempt to "short-circuit" that table by using "helper bits", e.g.…
John Russell
  • 2,177
  • 4
  • 26
  • 47
0
votes
1 answer

SQL Server Execution plan when combining multiple tables

I have a stored procedure that issues a query similar to the one below (pseudo-tsql). Multiple ParentIds are passed in as a parameter (csv), parsed, and inserted into a table variable @i. For each ParentId passed in, we look up the StorageTable and…
John Russell
  • 2,177
  • 4
  • 26
  • 47
0
votes
1 answer

Execution Plan reuse

Consider the following "code" define stmt1 = 'insert into T(a, b) values(1, 1); define stmt2 = 'select * from T'; MSSqlCommand.Execute( stmt1;stmt2 ); MSSqlCommand.Execute( stmt2 ); Investigating the cached query-plans using: SELECT…
Jens Nordenbro
  • 11
  • 4
0
votes
1 answer

SQL Server choice wrong execution plan

When this query is executed, SQL Server chooses a wrong execution plan, why? SELECT top 10 AccountNumber , AVERAGE FROM [M].[dbo].[Account] WHERE [Code] = 9201 Go SELECT top 10 AccountNumber , AVERAGE FROM [M].[dbo].[Account] with…
user3189030
  • 1
0
votes
2 answers

How to relate Execution Plan and SQL Query

Here is the query UPDATE hrs.rns_recon_ho c SET c.refr_numb = ( SELECT seqn_numb FROM hrs.rns_recon_ho p WHERE p.narr_1 = c.narr_1 AND p.seqn_numb = p.refr_numb AND p.prod_code = 0 ) Where c.prod_code = 0 And c.refr_numb = 0 AND c.narr_1 =…
bjan
  • 2,000
  • 7
  • 32
  • 64
0
votes
1 answer

Are these 2 sql queries equivalent in all respects (e.g. estimated and actual execution plan)?

Are query 1) == 2) in terms of estimated query plan AND actual plan? (can statistics affect the actual plan here, ever?) declare @cat int -- input param from prc ... 1) select * from A as a join B as b on b.id = a.id on b.cat = @cat …
Xerion
  • 3,141
  • 5
  • 30
  • 46
1 2 3
…
95 96