Tag Info

Hot answers tagged

9

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


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


1

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes. Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index. The missing index feature isn't always ...



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