The index on dimTime is changing. The quicker plan is using a _dta index. First off, make sure that isn't marked as a hypothetical index in sys.indexes.
Thinking you could be bypassing some parameterization by using the #mac table to filter instead of just supplying the start/end dates like this WHERE t.TimeValue between @StartDate and @enddate. Get rid of that temp table.
What is up with FROM part JOIN model ON 1=1
? This the same as FROM part, model
, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?
You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.
This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.
You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql
, as in the following example:
CREATE PROCEDURE [dbo].[create_grid_materials2]
(
@partlistid bigint
, @pid bigint
, @masterid bigint
)
AS
BEGIN
begin
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '
INSERT INTO material (partid, personid, modelID)
SELECT
partid = part.id
, personid = @pid
, modelid = model.id
FROM part
INNER JOIN model ON 1=1
WHERE (
model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + '
AND model.modelSetID IS NULL
AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
AND (
part.partType = 100
or part.partType=120
or part.partType = 130
)
)
AND NOT EXISTS (
SELECT 1
FROM material AS a1
WHERE a1.partid = part.id
AND a1.personid=@pid
AND a1.modelid=model.id
)';
DECLARE @Params VARCHAR(200);
SET @Params = '@pid INT';
EXEC sys.sp_executesql @cmd
, @Params
, @pid = @pid;
end
End
The above code will cause a new plan to be generated for each combination of @partlistid
, and @masterid
.
The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.
Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid
since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.
Best Answer
This is a bug in SQL Server (from 2008 to 2014 inclusive).
My bug report is here.
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 from being pushed down into the scan operator. The memory granted to the sort is now correctly based on the estimated cardinality of the Filter output, not the scan:
For a production system, steps will need to be taken to avoid the problematic plan shape (a filter pushed into a scan with a sort on another column). One way to do this is to provide an index on the filter condition and/or to provide the required sort order.
With this index in place, the desired memory grant for the sort is only 928KB:
Going further, the following index can avoid the sort completely (zero memory grant):
Tested and bug confirmed on the following builds of SQL Server x64 Developer Edition:
This was fixed in SQL Server 2016 Service Pack 1. The release notes include the following:
Tested and confirmed fixed on:
Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition
Both CE models.