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
1 answer

Not able to understand co-relation between cost keyword in explain plan with time

When I run below query explain select count(*) over() as t_totalcnt, max(hits) over() as t_maxhits, max(bytes) over() as t_maxbytes, * from ( select category,sum(hits) as hits,sum(bytes) as bytes from ( select…
jenitshah
  • 131
  • 2
  • 10
0
votes
0 answers

Identifying some parts of Query Plan in SQL Server

I have recently encountered a problem I have hard time working around. I have to tune a rather nasty query that extensively uses - in many forms and on several layers - XML. The problem is that while it is easy to spot the slow part of the whole…
nimdil
  • 1,361
  • 10
  • 20
0
votes
1 answer

Performance issue using new paging stynax in SQL Server 2012 (OFFSET n ROWS FETCH NEXT m ROWS ONLY)

I found a post about new paging stynax in SQL Server 2012. Like SELECT p.ProductName FROM Products p ORDER BY p.ProductID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY That was great. But when I look into Execution Plan, the cost is 100%. Does it mean…
Edi Wang
  • 3,547
  • 6
  • 33
  • 51
0
votes
2 answers

What exactly is saved in SQL Server Statistics? When they get updated? Is SQL Server itself is taking care of them?

I have been working with SQL Server as a Developer a while. One thing I learnt is SQL Server manages Statistics which help Engine to create optimized execution plan. I could not figure out what exactly is stores in Statistics? (I read it saves…
Pritesh
  • 1,938
  • 7
  • 32
  • 46
0
votes
2 answers

Execution Plan for a long stored proc cuts off. How to view it in SSMS?

I am trying to look at an execution plan for a sproc that has a massive amount of statements, looping, etc... As you can imagine, the execution plan is really long. In fact, SSMS gets confused before the interesting part that I am trying to look…
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
0
votes
1 answer

mysql index not optimizing query

I have mysql MyISAM table on which I am doing a simple select id from mytable limit 1;. This just freezes the system. I tried explain select id from mytable limit 1;. Again it freezes my system. Table demographics: 50k records, 10 mbs size, 2…
jerrymouse
  • 16,964
  • 16
  • 76
  • 97
0
votes
0 answers

Renaming table results in different Query Plan

Probably more information is needed, but this is really odd. Using SQL 2005 I am executing an Inner Join on two tables. If I rename one of the tables (using Alter Table), the resulting Query Plan is significanly longer. There are views on the…
Neil Weicher
  • 2,370
  • 6
  • 34
  • 56
0
votes
1 answer

Is it possible to optimise ShowPlan XML in SQL Server

I have a reasonably complex query that takes under a second to run once SQL server has established the query plan (satisfactory). However the first time the query runs the event ShowPlanXML according to the profiler takes about 14 seconds (not…
Tom Carter
  • 2,938
  • 1
  • 27
  • 42
0
votes
1 answer

What does it indicate when a table spool has more than one

According to MSDN, The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. First, there's no input in the plan before…
Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
0
votes
4 answers

Cached Query plans?

Query plans are stored in the plan cache for both views and ordinary SQL from here ok. Once and for all : How does it help me ? Even if I have the Query plans in the cache for a query : Once I run the query he WILL scan the whole table/s +…
Royi Namir
  • 144,742
  • 138
  • 468
  • 792
-1
votes
3 answers

difference between select count(*) and select count(fieldName)

Possible Duplicate: In SQL, what's the difference between count(column) and count(*)? As per subject, is there any difference in how MySQL interprets the above query? Or they are regarded as the same?
Andreas Wong
  • 59,630
  • 19
  • 106
  • 123
-1
votes
1 answer

SQL Server error - The SET SHOWPLAN statements must be the only statements in the batch

I am trying to generate a showplan table in SQL Server. I am getting this error when I try to do this Msg 1067, Level 15, State 1, Line 0 The SET SHOWPLAN statements must be the only statements in the batch. I tried on a simple query and the error…
SunnyBoiz
  • 514
  • 1
  • 5
  • 14
-1
votes
1 answer

How can I optimize DB query?

My db query is taking 5 seconds instead of <1 second earlier and the culprit query is : explain extended select A,B,C from table flow where (status in ('X' , 'Y' , 'Z')) and priority=1 and created<=now() order by id asc limit…
user124
  • 423
  • 2
  • 7
  • 26
-1
votes
1 answer

How to read costs in Postgres explain statements?

Here's an example explain from postgres: Aggregate (cost=55881.29..55881.30 rows=1 width=64) -> Nested Loop (cost=1509.25..55881.28 rows=1 width=32) -> Nested Loop (cost=1508.82..55880.82 rows=1 width=23) -> Nested…
Antonis Karvelas
  • 105
  • 1
  • 7
-1
votes
1 answer

Optimal explain plan

In theory, which of these would return results faster? I'm having to deal with almost half a billion rows in table and coming up with a plan to remove quite a few. I need to ensure I'm providing the quickest possible…
user3299633
  • 2,971
  • 3
  • 24
  • 38
1 2 3
…
95 96