I have seen one of those limits breached a number of times. It's the number of parameters (2,100) which applies to any query.

Amusingly I've had developers complain that this is not high enough and that there must be something wrong with SQL as it won't let them do what they want. It's not that big or complex a DB either (1TB or so, 700 tables).

The queries themselves don't come via a stored proc but via nHibernate. What you typically see is a query being passed in as follows :

SELECT blah FROM dbo.Table WHERE MyParam IN (@P0, @P1...@P2100)

This all depends on how many params get passed in, so if I have 2,099 I get

SELECT blah FROM dbo.Table WHERE MyParam IN (@P0, @P1...@P2099)

and the hash of the query changes, so we compile a new execution plan and so on.

Mix this in with another approach you see where it explicitly states the size of the values being passed in and you get another variation for the length of the parameter. If I'm passing in a surname with 7 characters, I get one execution plan and if I pass in a surname with 6 characters and I get another. You can get both together for extra fun !