I have a select statement within a procedure that is causing me some penalty at this time. The query estimates approximately 100 million rows and that in turn grants large amounts of memory (approximately 8 gigabytes per procedure call), which in turn results in extreme memory pressure on the database instance.
When the CE model is 120 (the instance is activated
SQL Server 2014 SP3, CU4 by the way), I get the following estimates
At the same time, if I set the CE model to 70 using
QUERYTRACEON 9481, I get perfect estimates
I am very interested in understanding why switching to an older CE model gives me a better estimate of the query.
In addition, I also see strange columns without statistical warnings.
But at the same time I can see that I have statistics for those columns in my table. I also tried to create a multi-column statistic, I thought I would try too. But it didn't really help either.
If I update the statistics, the problem is solved almost immediately by the way and creates a better new plan (to make sure that a recompilation doesn't really solve the problem, I deleted the procedure and hash from the cache plan before updating the statistics)
This is the selection statement in question:
, uc.FirstName AS CreatorFirstName
, uc.LastName AS CreatorLastName
, t.ItemID AS ID
, sl.ExpirationDate AS LockExpirationDate
, ea.Email AS LockOwnerEmailAddress
, ul.FirstName AS LockOwnerFirstName
, ul.LastName AS LockOwnerLastName
, sl.StreamLockTypeID AS LockTypeID
FROM #CurrentItemsWithPerms t
INNER JOIN dbo.Items i
ON i.ID = t.ItemID
INNER JOIN dbo.Users uc
ON uc.ID = i.CreatorID
LEFT OUTER JOIN dbo.StreamLock sl
ON sl.StreamID = t.StreamID
LEFT OUTER JOIN dbo.Users ul
ON ul.ID = sl.LockOwnerID
LEFT OUTER JOIN dbo.EmailAddresses ea
ON ea.UserID = ul.ID
AND ea.IsPrimary = 1
t.ItemID <> @ParentID
AND t.PermissionsFound = 1
Consultation plan for the CE 120 model: click here
Consultation plan for the CE 70 model: click here
So, just to summarize my questions:
- Why the old CE model does a better job estimating the rows in
- Why do I see warnings related to missing statistics when they are already present?