Hot answers tagged

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


18

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Consistency There is no general guarantee of consistency. Estimates may be calculated on different (but logically equivalent) subtrees at different times, using different statistical methods. There is ...


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


14

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

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

Memory Limit Exceeded The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed. Time Out This reason ...


11

The aggregate is a scalar aggregate (no group by clause). These are defined in SQL Server to always produce a row, even if the input is empty. For a scalar aggregate, MAX of no rows is NULL, COUNT of no rows is zero, for example. The optimizer knows all about this, and can transform an outer join into an inner join in suitable circumstances. -- NULL for a ...


11

I suspect the difference is the implicit conversion, which can get in the way. As an example, Bar isn't touched if it has a compatible data type: CREATE TABLE [dbo].[Bar] ( [Value] int NULL, [Value2] int NULL );


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


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

Preparing a SQL batch separately from executing the prepared SQL batch is a construct that is effectively** useless for SQL Server given how execution plans are cached. Separating out the steps of preparation (parsing, binding any parameters, and compiling) and execution only makes sense when there is no caching. The purpose is to save the time spent on ...


10

The most likely explanation is that your sessions have different settings. SQL Server has various session settings that can affect the execution plan selected (and the results!) The values for these settings can depend on how you connect to SQL Server, since different tools set the options different ways when they connect, and some (like SQL Server ...


10

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level. In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and ...


10

You actually have 595,947 matching rows, which is about 3% of your data. So the cost of the lookup adds up quickly. Suppose you have 100 rows per page in your table, that's 200,000 pages to read in a table scan. That's a lot cheaper than doing 595,947 lookups. With the GROUP BY clause in the question, I think you'll be better off with a composite key on (...


10

I want to know how the query execution works here The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example). After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator that ...


10

...why the huge performance hit from joining to sys.databases? And why is it inconsistent? There's nothing special about joining to sys.databases. The optimizer happens to choose a plan that is inefficient for the first query. Specifically, in this area of the plan: ...the optimizer chooses a nested loops join to SYSDMEXECCACHEDPLANS, presumably based ...


10

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


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

You are using a linked server to access Table1 and Table2. The first query is sent as is to the other server and executed there returning only the rows you want. The second query is doing a join between a local table TB_BRANCH and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the ...


9

The values of variables aren't generally sniffed so it will just assume a flat 30% of the table will be returned for that greater than predicate against an unknown value (cf. Selectivity Guesses in absence of Statistics). When you use the literal it can look up the known value in the column statistics to get a much more accurate estimate. If it estimates ...


8

SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false". This appears to be the property you are asking about. This is a sample (the last line shows the property): <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="...


8

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are: TimeOut MemoryLimitExceeded GoodEnoughPlanFound The articles you mention seem ok for finding these events, are you having a specific problem? The ...


8

The SOS_SCHEDULER_YIELD accumulation is just like I suggested on #sqlhelp. Each of those equates to 4ms of CPU time for the query, and they always show zero resource wait time, as there is no resource wait involved (thread yields the processor and goes directly to the bottom of the Runnable Queue on the scheduler). So - this query was churning through CPU ...


8

Execution plan still shows ClusterIndex Seek why? The initial seek down the b-tree is to find the first row where CustomerID >= 1. From that point on, the storage engine remembers the current scan position, and returns the next row in index order that qualifies each time a row is requested by a parent plan operator. The scan comes to an end as soon as ...


8

The thing to remember here is that execution plans suck the data through. So the Nested Loop operator calls the Stream Aggregate 4 times. The Stream Aggregate calls the Filter 4 times as well, but only gets a value twice. So the Stream Aggregate gives four values. Twice it gives a value, and twice it gives Null.


7

You could add a calculated column to the table and build an index from the calculation. For instance, the table would be: CREATE TABLE dbo.InverterData ( InverterID bigint NOT NULL , TS datetime NOT NULL , ValueA decimal(18, 2) NULL , ValueB decimal(18, 2) NULL , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0)) ,...


7

Now my question is ,since nested loops does a key lookup once for each row returned from seek,should seek reads be 25*3 :75 same as key lookups If the question is "should the seek also require 75 reads?" then the answer is no, for the reasons Itzik gave, and quoted in the question: Seek to the leaf of index: 3 reads (the index has three levels) ...


7

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan. You can write a query to find plans that: Contain a Hash Match Aggregate; and The query text contains a GROUP BY clause ...which is not quite the same thing, since this will find plans where the grouping logic was ...



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