Hot answers tagged

47

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 ...


38

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 ...


28

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 ...


27

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 ...


25

The semantics of the two statements are different: The first does not set the value of the variable if no row is found. The second always sets the variable, including to null if no row is found. The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left ...


24

The Query Optimizer in SQL Server can make multiple missing index suggestions for individual queries. However the part of SQL Server Management Studio (SSMS) which displays execution plans visually only displays a single missing index suggestion; it looks like a bug. However these multiple index suggestions are visible in SSMS, eg in the properties for the ...


24

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 ...


21

This is a bug in SQL Server (from 2008 to 2014 inclusive). My Connect bug report is here. The filtering condition is pushed down into the scan operator as a residual predicate, but the memory granted for the sort is erroneously calculated based on the pre-filter cardinality estimate. To illustrate the issue, we can use (undocumented and unsupported) trace ...


19

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' ) ...


19

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 ----------- ----------- ...


17

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 ...


17

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 ...


17

Before getting to the main answer, there are two pieces of software you need to update. Required Software Updates The first is SQL Server. You are running SQL Server 2008 Service Pack 1 (build 2531). You ought to be patched up to at least the current Service Pack (SQL Server 2008 Service Pack 3 - build 5500). The most recent build of SQL Server 2008 at the ...


16

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions. The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the ...


15

And nothing about the functions. Why is the function information missing in the actual plan? This is by design, for performance reasons. Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains ...


14

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available. From the execution plan provided, this is exactly what happened in your case. When a ...


13

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 ...


13

How to find out what the cost of creating a query plan is? You can look at the properties of the root node in the query plan, for example: This information is also available by querying the plan cache, for example using a query based on the following relationships: WITH XMLNAMESPACES (DEFAULT ...


12

The query optimizer has a number of choices when constructing an execution plan for this query. Among the many strategies available, it can choose between hash join and nested loops join. Which one it decides to use depends sensitively on the statistical information available, and other factors like the amount of memory configured for SQL Server to use. It ...


12

Given these constants, will SQL Server always produce the same plan for a given query? If not, are there other considerations? Is there also an element of nondeterminism to consider as well? Query compilation is deterministic as far as I am aware. One of the original QO design goals was that it should be possible to reproduce execution plans on a ...


12

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 ...


12

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 ...


12

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here. In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID ...


11

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 ...


11

The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this. They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...


11

There isn't an explicit way to do this today, but that isn't a permanent scenario (the DBCC command is still not supported, but read up on Query Store). 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 ...


11

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 ...


11

Yes. You need the USE PLAN hint. In which you supply the XML from the first plan. SELECT * FROM T OPTION (USE PLAN N'<?xml version="1.0" encoding="utf-16"?> ....') Whilst it doesn't guarantee that the plan will be exactly the same (e.g. compute scalar operators can move around for example) it will likely be pretty close.


11

I guess you are comparing the estimated costs for the queries. Those are just estimates based on (among other things) the estimated number of rows returned by the query. Not the actual number of rows. Your first query estimated that it would return 30 rows and your second query estimated 1000 rows. That is where your difference in query cost comes from. If ...


11

I think my understanding of the second estimate is incorrect and the differing numbers seems to indicate that. What am I missing? Using the SQL Server 2012 cardinality estimator, the selectivity of the join drives the estimated number of rows on the inner side of the nested loops join, and not the other way around. The 11.4867 number is derived (for ...



Only top voted, non community-wiki answers of a minimum length are eligible