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


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


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


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

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

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

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

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


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


8

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


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


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

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


5

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


5

If this was a one-off operation, I would be inclined to use SQL Sentry Plan Explorer (it's free!), rather than faff around writing a SQL Script. Open the Execution Plan in Plan Explorer, navigate to the Query Columns pane and you can see a list of the objects that are used in the query.


5

Internally, there are two separate forms of IN, as well as for the ANY construct. One of each, taking a set, is equivalent to the other and expr IN (<set>) also leads to the same query plan as expr = ANY(<set>) that can use a plain index. Details: IN vs ANY operator in PostgreSQL Consequently, the following two queries are equivalent and ...


5

Regarding this I wonder if the prepare() method is kind of useless or obsolete or if I am missing something here? I would say that the Prepare method has limited value SqlCommand world, not that it's entirely useless. One benefit is that Prepare is part of the IDbCommand interface that SqlCommand implements. This allows the same code to run against ...


5

Yes, you can get this information. It is buried inside properties of the select statement in an actual execution plan. To do that: Set Actual Execution plan to on Execute your SP with recompile option Go to Actual Execution plan, choose 'select' part Go to properties (Press F4) and there you can see CompileTime However, I'm not sure how accurate or what ...


5

In the particular scenario presented: No, the difference between estimated and actual number of rows is not important. There are two key pieces of information to support that statement: The query has qualified for a trivial plan; and The query has further qualified for simple parameterization This is clear from the Seek Predicate text, where the literal ...


5

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's. As stated in this blog post on msdn You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix. SQL Server 2014 Service ...


5

To get a clustered index seek, you'd need a clustered index that supports your filter (e.g. leading key would have to be Personal_ID, not ID). You can't force a seek if there's no index with a leading column of Personal_ID that supports the filter. This does not mean you should change the existing clustered index, unless this is the only query you ever ...



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