Tag Info

Hot answers tagged

25

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


17

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


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

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


16

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


15

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


15

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


15

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

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


12

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

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


11

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


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

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


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

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

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


9

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


9

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


9

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


9

I don't think this query is about TVPs so much as complexity. The plan has an optimizer timeout. You can see it in the F4 properties of the SELECT operator as "Reason for Early Termination of Statement = Time Out" or StatementOptmEarlyAbortReason="TimeOut" in the plan xml. These are not always a disaster, it just means the optimizer has run out of time ...


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

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

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


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


8

This table is very small! It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints). CREATE TABLE Person.ContactType( ContactTypeID int IDENTITY(1,1) NOT NULL, Name dbo.Name NOT NULL, ModifiedDate datetime NOT NULL, CONSTRAINT ...


7

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


7

Remove it by making the index covering That is, add an INCLUDE clause to the index so all columns needed are in the index.



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