Home Dashboard Directory Help
Search

Statistics estimation for LIKE on columns with trailing spaces can be very wrong. by Martin Smith


Status: 

Active


5
0
Sign in
to vote
Type: Bug
ID: 763060
Opened: 9/17/2012 8:08:01 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

Repro shows an example where 100% of the table rows match a predicate, the statistics are updated with full scan and accurate but the estimated number of matching rows is orders of magnitude too small and a sub optimal plan chosen.

I originally saw it here in the context of a char datatype column http://stackoverflow.com/q/12455730/73226 but have also found that changing the definition of the column in the repro to varchar(8) and inserting the value 'DAV     ' exhibits the same problem.
Details
Sign in to post a comment.
Posted by Martin Smith on 9/18/2012 at 12:14 PM
Hello,

The repro with the varchar is probably less of an issue in that it is less likely to be stored with trailing spaces anyway. I've just done a quick test again and found that for some odd reason on one instance I tested it on if run in the context of the master database the script I am using doesn't manifest the issue until the second SELECT.


IF OBJECT_ID('[dbo].[M8]', 'U') IS NOT NULL
    DROP TABLE [dbo].[M8]

GO

CREATE TABLE [dbo].[M8](
[M8_ID] [int] IDENTITY(1,1) NOT NULL,
[APPLIC] [varchar](8) NOT NULL
)

CREATE NONCLUSTERED INDEX INDEX_A ON [dbo].[M8]([APPLIC])

INSERT INTO [dbo].[M8]
SELECT TOP 2000 'DAV     '
FROM master..spt_values

SELECT *, DATALENGTH([APPLIC])
FROM M8
WHERE M8.APPLIC LIKE 'DAV'

SELECT *, DATALENGTH([APPLIC])
FROM M8
WHERE M8.APPLIC LIKE 'DAV'
Posted by Microsoft on 9/18/2012 at 10:02 AM
Thanks for taking the time to file this issue. This is a bug and we will fix it for the next release.
I am unable to reproduce the problem using the type "varchar" and was going to suggest that as a workaround. Please let me know your experience or share a repro using "varchar". Thank-you.

sincerely
Campbell, SQL Development
Posted by Xavier GEHL on 9/17/2012 at 8:24 AM
I came across this problem.

If you need more infos, I'm here.
Sign in to post a workaround.