Tag Info

Hot answers tagged

20

This is a bug in SQL Server (from 2008 to 2014 inclusive). 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 flag 9130 to prevent the Filter ...


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


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

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


8

when does SQL Server notice the new indexes and determines whether they actually help the query? Immediately. Creating an index causes a recompile because of a schema change. Here's an illustration of this. First to setup the objects in the test database: use TestDatabase; go if exists (select 1 from sys.tables where object_id = ...


7

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior? The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of ...


7

is it possible to view the locks, along with the type, acquired during the execution of a query? Yes, for determining locks, You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as ...


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


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


5

1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%. The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


4

Is this behaviour correct? It is intentional (by design) behaviour; whether it is 'correct' or not is more a matter of opinion. The general point is: SQL Server does not guarantee the timing or number of evaluations of expressions. This behaviour exists to give the query optimizer the freedom it needs to find good execution plans. As a consequence, ...


4

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? No, what happens is that the execution plan for a stored procedure is cached. Assuming there is enough available memory to continue holding the plan, it won't change unless one of the ...


4

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one. A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan. In some cases, too many indexes on a table can also cause this, as the optimizer might use an index that is not the best one because as ...


4

How does the query processor execute COMPUTE BY? It is a hard-coded sequence of operations at the top of the execution tree. There is a simple stream accumulation (hence the ordering requirement), some logic to detect the start of a new group, a little computation and data value copying, and direct calls to construct the alternate TDS result sets per ...


3

Am I correct in saying that statistics are only used when creating the execution plan No, out-of-date statistics may cause an optimality-related recompilation of the affected statement. We have an ascending date/time column in one of our largest tables that we query regularly Sub-optimal execution plans caused by predicate values being outside ...


3

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...


3

The decision to use a table spool or not depends on the query optimizer's cost assessments. These in turn are influenced by the specific values of parameters when the query plan is compiled (or recompiled). So, this is just a regular case of parameter sensitivity. The chosen (and cached) plan depends on the parameter values sniffed when a compilation or ...


3

You're on the right track - the XML index is the problem. Obviously, there's a primary as well as a secondary XML index. When performing a DELETE against the base table (ETLHeaders) the data have to be deleted from every index of this table, too. This overhead can be significant, especially for XML indexes. The index causing the long duration is the ...


3

Interesting. It seems this is due to the addition of the unique constraint, which adds an index on ID, which in turn causes the optimizer to use an index seek rather than a table scan. Unfortunately I have no idea why the index seek doesn't seem to throw a divide by zero error, as it shows the division in the seek predicate. DECLARE @X TABLE ( ID Int ...


3

I believe Kendra is using a front end application called SQL Sentry Plan Explorer. It's a really great tool and gives a little bit more verbose info about the execution plan. Here's an example of a query I'm working on right now with Plan Explorer: It's also free-ninety-nine! (the paid version gives you some more bells and whistles but the free version ...


3

The link provided by Martin Smith to an existing SO question provided me with the answer: The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


3

Since it seems clear that a statistic already exists for the particular column, I offer two possibilities: Object Explorer is pointed to a different database - I'm suspicious because the database has DEV in the name, so it's possible that in one case you're looking at dev and in another you're not. The plan being used is from before the statistic was ...


3

This is not an issue, it is by design and is considered a performance improvement. If your queries do not run as expected you will need to address this in the code of your database/application. You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level. This will force it no matter what the compatibility ...


3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON ...


3

NOT EXISTS can introduce a TOP 1 (as can EXCEPT in some cases). This is because it only cares about the fact that at least one row exists. This is called short circuiting, and you want this - it is much more likely to speed up your query than slow it down. And I'd be really surprised if the TOP didn't show up in the plan for different date ranges, unless ...


3

GetItemToProcessIndex is not fully seekable because your where clause is on ItemState + LastAccessTime + CreationTime. The indexed columns and the where clause are a not perfect match. If you create a covering index on ItemState + LastAccessTime + CreationTime, for each match you get from GetItemToProcessIndex, you also get the value of your Primary Key ...



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