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 ...
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
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
...
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 ...
9
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
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
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 ...
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
...
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
Try SET STATISTICS XML ON before you run your query. That will return the plan in XML format without trying to render it in SSMS. You could then copy and paste the text into a file. From there, you'll get the plan in XML format, and you can open it up in a tool like SQL Sentry Plan Explorer.
5
Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but...
How Can the Same Query in Two Nearly Identical Instances Generate Two
Different Execution Plans?
Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics.
Table rows ...
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 ...
4
Let me try to explain why you should not do that, why you should never assume that an SQL-product will return a result set in a specific order, unless you specify so, whatever indices - clustered or non-clustered, B-trees or R-Trees or k-d-trees or fractal-trees or whatever other exotic indices a DBMS is using.
Your original query tells to the DBMS to ...
4
If you're asking why one of these plans is more expensive than the other one:
Your first plan performs a sort on a column that is not indexed (or not part of the index that is being used to retrieve the data). Therefore once the data is retrieved, because you've asked for an ORDER BY, the results have to be re-sorted (since the clustered index will return ...
4
Your test design is flawed. You are testing incorrect results.
I added an answer to the question on SO you are referring to.
In your CASE version you can't add ORDER BY col1, col2. Would have to be ORDER BY precedence. But it would still be incorrect. You would have to ORDER BY the sum of scores for individual conditions to get rows fulfilling the most ...
4
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
With a slight fiddling of your query I get an equal cost estimate (50/50) and equal IO stats:
; WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #objects
)
SELECT *
FROM cte
WHERE r >= 30 AND r < 40
ORDER BY r
SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY
This avoids the additional ...
4
The cost is the same (1%) for both the slow and fast cases. Does that
mean the warning can be ignored? Is there a way to show "actual" times
or costs. That would be so much better! Actual row counts are the same
for the operation with the spill.
The cost shown is always the optimizer's estimated cost of the iterator, computed according to its ...
3
I've had this happen from time to time. The option to SET STATISTICS XML ON as referenced above is probably the best answer (outside of using a thrid party). However, I've found that running SSMS on the server itself can help. That said, if you are crashing SSMS on your local box, probably not a great idea to try it on the server.
Another thing you can ...
3
Your statistics need updating.
See Statistics, row estimations and the ascending date column.
Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the >= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested ...
3
When you use TOP, the Optimizer sees an opportunity to do less work. If you ask for 10 rows, then there's a good chance it doesn't need to consume the whole set. So the TOP operator can be pushed much further to the right. It will keep requesting rows from the next operator (on its right), until it has received enough.
You point out that without TOP, the ...
3
SQL Server 2005+ has statement level recompilation. Therefore, IIRC you'll get an optimal plan depending on branch.
However, your code isn't concurrency-safe. Two overlapping calls can get true for the NOT EXISTS: you get an error. This is why you use MERGE on SQL Server 2008.
Or assorted JDFI patterns for older versions. Also see Need Help Troubleshooting ...
3
I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.
To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and ...
3
Removing Execution Plans from the Procedure Cache
SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache. However, if you make changes to the to a table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache. Ref: here
2
You need to use SQL baselines to force the execution plan.
This blog describes the steps involved. You'll need the sql_id for the statement and the plan_hash_value of the old plan.
Only top voted, non community-wiki answers of a minimum length are eligible
