Hot answers tagged

37

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


36

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


25

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


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


18

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

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

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


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

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


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


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

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


11

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected. Join Simplification in SQL Server Designing for simplification (SQLBits recording) * The optimizer does not currently recognize filtered unique indexes as ...


11

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


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

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

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

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

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


10

Obvious disclaimer: I work for SQL Sentry. The biggest problems we have are: Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. We are subject to the same limitations because we can only ...


10

Think about what "actual" means. It's what actually happens for the execution of that plan. Another common name for the actual execution plan is the "post execution plan". As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But ...


10

Check to ensure the database compatibility level is the same on the 2 servers. I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower. Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.


10

The most likely situation is that the new SQL 2014 Cardinality Estimator is yielding a poor row estimate for one or more joins in your query and this has led SQL Server to choose an inefficient plan. If you are able to run the query in SQL 2014 with "include actual execution plan" turned on, you can use the query below in another tab to view the real-time ...


9

Your Predicate is different to your Seek Predicate. A Seek Predicate is used to search the ordered data in the index. In this case, it'll be doing three seeks, one for each ItemState that you're interested in. Beyond that, the data is in ItemPriority order, so no further "Seek" operation can be done. But before the data is returned, it checks every row ...


9

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


9

The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below. For the other two nonclustered ...


9

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



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