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


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


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

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

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

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


9

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


9

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


9

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


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


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


8

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


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


7

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


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


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

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

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however. Using an example based on the article here a possible rewrite might be SELECT MAX(ID) AS ID FROM sys.partitions AS P ...


6

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality. UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000 And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER ...


6

Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...


6

According to the docs there are three ways to recompile a stored procedure and/or the queries inside of a stored procedure. WITH RECOMPILE This is probably the most direct way to force a recompile, if you are willing to edit the definition of the stored procedure then you can automatically force a recompile every time. There are some significant ...


6

The field in your WHERE condition is not the leading field of the index. You have measure defined as NVARCHAR so prefix the literal with an N: where Measure = N'FinanceFICOScore'. Consider creating a Clustered Index on SnapshotKey. If it is unique then it can be a PK (and Clustered). If not unique then it cannot be a PK, but can still be a non-unique ...


6

I believe you will have to do some XML query work to get that estimated cost. See if this is what you are looking for: ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT TOP 1000 st.text ,cp.size_in_bytes ,cp.plan_handle ,QP.query_plan ...


6

You are probably running into parameter sniffing issues. The fact that you execute the query with a different ARITHABORT setting makes SQL Server create a new plan and not reuse the existing plan as that setting is part of the cache key. Have a look at Slow in the application, fast in SSMS, it has a lot of information such as this: We have seen that ...



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