Hot answers tagged execution-plan
16
One way to get an index spool to appear naturally is to express the requirement using slightly different syntax:
SELECT DISTINCT
z.a
FROM dbo.t5 AS z
JOIN dbo.t4 AS y ON
y.a >= z.a AND y.a <= z.a
OPTION (LOOP JOIN, MAXDOP 1, FORCE ORDER);
This produces an execution plan like:
Rewriting the equality as a pair of equivalent inequalities ...
14
The constant scans each produce a single in-memory row with no columns. The top compute scalar outputs a single row with 3 columns
Expr1005 Expr1006 Expr1004
----------- ----------- -----------
NULL NULL 60
The bottom compute scalar outputs a single row with 3 columns
Expr1008 Expr1009 Expr1007
----------- ----------- ...
13
NOT IN (SELECT ...) and NOT EXISTS (SELECT .. WHERE correlation..) are "Anti Semi Joins". That is, recognised set based operations
WHERE NOT (MyColumn = 1) is a filter that requires all rows to be looked at
For more info, see:
Craig Freedman's "Introduction to Joins"
Wikipedia "Relational algebra, Antijoins"
Edit: for completeness
LEFT JOINs often ...
13
Short version: seek is much better
Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...
12
I would have guessed that when a query includes TOP n the database
engine would run the query ignoring the the TOP clause, and then at
the end just shrink that result set down to the n number of rows that
was requested. The graphical execution plan seems to indicate this is
the case -- TOP is the "last" step. But it appears there is more going
...
12
The query is
SELECT SUM(Amount) AS SummaryTotal
FROM PDetail WITH(NOLOCK)
WHERE ClientID = @merchid
AND PostedDate BETWEEN @datebegin AND @dateend
The table contains 103,129,000 rows.
The fast plan looks up by ClientId with a residual predicate on the date but needs to do 96 lookups to retrieve the Amount. The <ParameterList> section in ...
12
Because we know that l.id = '732820' and ls.id = l.id then SQL Server derives that ls.id = '732820'
i.e.
FROM db2.dbo.VIEW ls
JOIN db1.dbo.table l
ON ls.id = l.id
WHERE l.id = '732820'
is the same as
( /*...*/ FROM db2.dbo.VIEW ls WHERE id = '732820' )
CROSS JOIN
( /*...*/ FROM db1.dbo.table l WHERE id = '732820' )
...
11
The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.
As for the question: statistics are in the database so a backup/restore will preserve the statistics.
Note though that ...
10
The constant scans are a way for SQL Server to create a bucket into which it's going to place something later in the execution plan. I've posted a more thorough explanation of it here. To understand what the constant scan is for, you have to look further into the plan. In this case, it's the Compute Scalar operators that are being used to populate the space ...
10
The reason for the performance difference lies in how scalar expressions are handled in the execution engine. In this case, the expression of interest is:
[Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)
This expression label is defined by a Compute Scalar operator (node 11 in the serial plan, node 13 in the parallel plan). Compute Scalar ...
9
So, my question is this... how can parameter sniffing be to blame when
we get the same slow query on an empty plan cache... there shouldn't
be any parameters to sniff?
When SQL Server compiles a query containing parameter values, it sniffs the specific values of those parameters for cardinality (row count) estimation. In your case, the particular ...
8
When the
table is small enough there is no practical difference
statistically, you'd return most rows anyway
The 2nd case needs qualifying
An index scan will replace an index seek if an index is covering
An index seek or scan with many rows that requires key/bookmark lookups will be expensive and a table scan could be better
Finally
An index scan ...
8
Reading & Comparing execution plans is a big topic. Let me add some basic information. First how do we get the execution plan? There are a couple of ways each with different benefits:
Query Analyzer (SSMS) -> Estimated Execution Plan - This gives you a graphical representation of an estimated execution plan. This is not always accurate but is useful ...
8
From http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/
"The hash join is one of the more expensive join operations, as it requires the creation of a hash table to do the join. That said, it’s the join that’s best for large, unsorted inputs. It is the most memory-intensive of any of the joins
The hash join first reads one of ...
8
As SQLRockstar's answer quotes
best for large, unsorted inputs.
Now,
from the Users.DisplayName index scan (assumed nonclustered) you get Users.Id (assuming clustered) = unsorted
You are also scanning Posts for OwnerUserId = unsorted
This is 2 unordered inputs.
I'd consider an index on the Posts table on OwnerUserId, including Title. This will ...
8
Just to summarise the experimental findings in the comments this seems to be an edge case that occurs when you have two computed columns in the same table, one persisted and one not persisted and they both have the same definition.
In the plan for the query
SELECT id5p
FROM dbo.persist_test;
The table scan on persist_test emits only the id column. The ...
8
Have you looked at SQL Sentry Plan Explorer? There is a free and a PRO version, and one of the benefits common to both is that they can handle humongous plans that Management Studio chokes on. You can also generate both actual and estimated plans from within the tool, so you don't even have to bother with SSMS.
disclaimer: I work for SQL Sentry
8
Addition to what Remus has mentioned, I would suggest you read --
SQL Server Statistics Questions We Were Too Shy to Ask
Aarons answer to - Where are Statistics physically stored in SQL Server?
UNDERSTANDING SQL SERVER STATISTICS
7
It isn't stored in sys.dm_exec_cached_plans, nor is it buried anywhere in the plan XML that I can find. There is useful information in other DMVs however.
For stored procedures we can get the time a plan was cached from sys.dm_exec_procedure_stats:
SELECT TOP(250)
p.name AS [SP Name]
, ps.execution_count
, ps.cached_time
FROM
...
7
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error). The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general:
DECLARE
@PageSize bigint = 10,
@PageNumber integer = 3;
WITH Numbered AS
(
SELECT TOP ...
6
First thought...
If you have sufficient data to take this much % of the batch, use a #temptable.
When the table variable is used later, it is always assumed to have one row: there are no statistics on this table variable. So if you have several 1000, subsequent plans won't be optimal.
Temporary tables have statistics (and indexes if required etc) and can ...
6
The advantage of hashing a numeric field is that you're taking a bigger value and breaking it down into smaller pieces so that it can fit into a hash table.
Here's how Grant Fritchey describes it:
"A hash table, on the other hand, is a data structure that divides all of the elements into equal-sized categories, or buckets, to allow quick access to the ...
6
Take a look at http://msdn.microsoft.com/en-us/library/ms189602.aspx:
Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission
can view queries that are captured in Showplan output. These queries
may contain sensitive information such as passwords. Therefore, we
recommend that you only grant these permissions to users who are
...
6
The funny thing is, that is considered a guide to execution plans, rather than a complete manual. I can say first hand that the reference mentioned is worth the effort and time to read. You need to become quite profficient with the topic of execution plans and how the SQL Server database storage engine works under the hood, in order to be able to deliver ...
6
When you have a clustered index on a table, the clustered index IS the table!
Mentally you can substitute "table" for "clustered index" in this instance and it will make sense.
The data for every field in every row is in your clustered index. The clustered index just sets the order of the physical pages in the database to be organized by your clustering ...
6
First, the costs are not supposed to directly relate to the execution time. They're strictly relative; a plan which costs more should take longer to actually execute. You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.
For 99% of users, there's only three cost ...
6
That is the code from my article originally posted here:
http://www.sqlservercentral.com/articles/deadlock/65658/
If you read the comments you will find a couple of alternatives that don't have the performance problems that you are experiencing, one using a modification of that original query, and the other using a variable to hold the XML before ...
6
There isn't an explicit way to do this today, but that isn't a permanent scenario (can't reveal more due to NDA). Even when the schema change hit is acceptable, it may not be what you want, because it will invalidate all plans related to the underlying object, not just the bad one.
Not looking for credit for this, but building dynamic SQL to perform the ...
5
A good optimizer will recognize when a correlated query could have been written as a join and use the same plan. Many correlated queries could be just as easily written as a join. In this case the optimizer is doing its job. Optimizing the query in this manner allows plans which would not make sense using the correlated query.
I tend to write a ...
Only top voted, non community-wiki answers of a minimum length are eligible
