21

While working with C# Entity Framework I noticed a crash of my SQL Server instance.

I was able to track it down to this statement:

SELECT * FROM dbo.[TestTable]
where mpnr in (1099059904,
1038139906,
1048119902,
1045119902,
1002109903,
1117109910,
1111149902,
1063149902,
1117159902,
1116109904,
1105079905,
1012079906,
1129129904,
1103059905,
1065059905,
1091059906,
1110149904,
1129149903,
1083029905,
1080139904,
1076109903,
1010019902,
1058019902,
1060019903,
1053019902,
1030089902,
1018149902,
1077149902,
1010109901,
1011109901,
1000119902,
1023049903,
1107119909,
1108119909,
1106119909)

The table looks like this:

CREATE TABLE dbo.[TestTable]([MPNR] [numeric](9, 0) NOT NULL)

The crash occurs every time I start the query. If I reduce the number of values within the IN clause, it works. (It returns no rows, of course.)

I'm aware that the values in the IN clause are 10-digit numbers and the column has only 9-digits, but that should not lead to a crash of the whole SQL Server instance.

My SQL Server's version is 2008 R2 on a Windows Server 2003 32-bit.

Is this a known Bug? Is there a Patch for SQL Server?

Michael J Swart
  • 2,235
  • 5
  • 23
  • 32
SteLoe
  • 362
  • 1
  • 9

1 Answers1

21

I was able to repro on 2008 R1 SP3 10.00.5512 but installing the latest CU (14) fixed it.

Reviewing the bugs fixed in the intervening versions it looks as though you need to upgrade to a build that includes the following fix.

Access violation when you run a query that contains many constant values in an IN clause in SQL Server 2008 or in SQL Server 2012

As you are on 2008 R2 you will need at least CU 9 for SP1 or CU 5 for SP2.

The description of symptoms is somewhat brief but mentions mismatched datatypes

When you run a query that contains many constant values in an IN clause in Microsoft SQL Server 2008, Microsoft SQL Server 2012 or in Microsoft SQL Server 2008 R2, an access violation might occur.

Note For the issue to occur, the constants in the IN clause cannot match exactly with the column data type.

It doesn't define "many". From the testing I did I suspect this may mean "20 or more" as this seems to be the cut off point between two different methods of estimating cardinality.

The crash was happening inside a couple of methods called by CScaOp_In::FCalcSelectivity() with names such as LoadHistogramFromXVariantArray() and CInMemHistogram::FJoin() -> WalkHistograms().

For 19 or fewer distinct in list items these methods weren't getting called at all. A similar SQL Sever 2000 bug also mentions this cut off point as significant.

Populating a test table with 100,000 rows of random test data with values between 0 and 1047 and a histogram starting as follows

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
|            0 |          0 |     104 |                   0 | 1              |
|            8 |        672 |     118 |                   7 | 96             |
|           13 |        350 |     118 |                   4 | 87.5           |
|           18 |        395 |     107 |                   4 | 98.75          |
|           23 |        384 |      86 |                   4 | 96             |
|           28 |        371 |      85 |                   4 | 92.75          |
+--------------+------------+---------+---------------------+----------------+

The query 

SELECT * FROM dbo.[TestTable]
where mpnr in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)
option (maxdop 1)

Shows estimated rows of 1856.

This is exactly what would be expected by getting the estimated rows for the 19 equality predicates individually and adding them together.

+-------+----------------+-------+
| 1-7   | AVG_RANGE_ROWS | 96    |
| 8     | EQ_ROWS        | 118   |
| 9-12  | AVG_RANGE_ROWS | 87.5  |
| 13    | EQ_ROWS        | 118   |
| 14-17 | AVG_RANGE_ROWS | 98.75 |
| 18    | EQ_ROWS        | 107   |
| 19    | AVG_RANGE_ROWS | 96    |
+-------+----------------+-------+

7*96 + 118 + 4*87.5 + 118 + 4*98.75 + 107 + 1*96 = 1856

The formula no longer works after 20 is added to the in list (Estimated rows 1902.75 rather than the 1952 that adding another 96 to the total would generate).

BETWEEN seems to use yet another method of calculating cardinality estimates.

where mpnr BETWEEN 1 AND 20 estimates only 1829.6 rows. I've no idea how that is derived from the histogram shown.

Martin Smith
  • 87,941
  • 15
  • 255
  • 354