Tag Info

New answers tagged

2

It's not possible to save query plans "for all future sessions". Query plans are only ever saved for the current session. And only reused under a number of favorable conditions. Plans for ad-hoc SQL queries are not saved at all. All queries inside PL/pgSQL functions are treated like prepared statements. And there are more steps than just the query planning. ...


1

From SQL 2012 onwards you could look for a large discrepancy between SerialRequiredMemory and SerialDesiredMemory, eg something like this: -- Search plan cache for Memory Grant issues IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp -- Collect more info about the plan here if required, eg usecounts, objtype etc, SELECT IDENTITY( INT, 1, 1 ) rowId, ...


10

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


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


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


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


0

OMem - Estimated amount of memory needed to perform the operation in memory only. This is also called the optimal execution. 1Mem - Estimated amount of memory needed to perform the operation in one pass (write to and read from disk (temp) only once). This is called a one-pass execution. A multi-pass execution is where the same data is written to and read ...


0

In your version of the query with forceseek, it looks like the majority of the query cost is located in the key lookup. You could eliminate this key lookup by creating a non clustered index on GridRunId and MancoId. Have a look at the estimated cost figure in the select part of your plans. The optimizer will chose the one with the lowest number. By adding an ...


0

Problem in the statistics of this query. SQL Server can't make right estimation. Look at [Project3] subquery. In that subquery you use two subqueries with "top 1" but you don't use "order by" clause. In this case no one can guarantee the same result on every run, even sql server. SQL Server don't know what result will be in [Limit1] subquery. Therefore it ...



Top 50 recent answers are included