added 4 characters in body; edited title
Source Link
Paul White
  • 65.1k
  • 25
  • 364
  • 566

SQL Server - Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKELIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When

When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

SQL Server - Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

Cardinality Estimate for LIKE operator (Local Variables)

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses).

When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

Incorporated comment
Source Link
Paul White
  • 65.1k
  • 25
  • 364
  • 566

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

I couldn't find anything online other than this Itzik Ben-Gan article, which states "When using the LIKE predicate in all optimize for unknown scenarios both the legacy and new CEs use a 9 percent estimate.". The information in that post would appear to be incorrect.

changed 214.416 to 241.416, author said it was a typo in a comment to my answer
Source Link

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 214241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 214241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 214241.416 is derived.

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 214.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 214.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 214.416 is derived.

I was under the impression that when using the LIKE operator in all optimise for unknown scenarios both the legacy and new CEs use a 9% estimate (assuming that relevant statistics are available and the query optimiser doesn't have to resort to selectivity guesses). When executing the below query against the credit database I get different estimates under the different CEs. Under the new CE I receive an estimate of 900 rows which I was expecting, under the legacy CE I receive an estimate of 241.416 and I can't figure out how this estimate is derived. Is anyone able to shed any light?

-- New CE (Estimate = 900)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName;

-- Forcing Legacy CE (Estimate = 241.416)
DECLARE @LastName VARCHAR(15) = 'BA%'
SELECT * FROM [Credit].[dbo].[member]
WHERE [lastname] LIKE @LastName
OPTION (
QUERYTRACEON 9481,
QUERYTRACEON 9292,
QUERYTRACEON 9204,
QUERYTRACEON 3604
);

In my scenario, I already have the credit database set to compatibility level 120, hence why in the second query I'm using trace flags to force the legacy CE and to also provide information on what statistics are used/considered by the query optimiser. I can see the column statistics on 'lastname' are being used but I still can't work out how the estimate of 241.416 is derived.

Tweeted twitter.com/StackDBAs/status/791787372719529984
Source Link
Fza
  • 630
  • 1
  • 7
  • 17
Loading