Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
I'm not saying that the result should be a crash, but why are you checking for 10-digit values that can't possibly fit into a NUMERIC(9,0)? –  Aaron Bertrand Dec 17 '13 at 17:39
4  
Wow. I can repro this. My test instance is 10.50.2811. I don't know if this is a known bug, but definitely contact Microsoft either using a support ticket or on Connect. –  Jon Seigel Dec 17 '13 at 17:56
7  
I've popped a repro script out on github especially since someone broke sqlfiddle trying it ;) –  billinkc Dec 17 '13 at 18:55
4  
Can confirm that I broke sqlfiddle trying this query –  Lamak Dec 17 '13 at 19:01
6  
For me < 20 numbers fine. 20-23 kills spid with "A severe error". More crashes the whole thing. Can't see this on Connect. You should report it. –  Martin Smith Dec 17 '13 at 19:31

1 Answer 1

up vote 19 down vote accepted

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.

share|improve this answer
3  
+1 Excellent find !! –  Kin Dec 18 '13 at 15:39
3  
Confirmed. On our 2008R2SP2-Testserver we installed CU10. Problem fixed. Thx for your help. –  SteLoe Dec 18 '13 at 16:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.