Slow "Select" Query with varchar(max)

Question

I have a small table with 500 rows. This table has 10 columns including one varchar(max) column.

When I perform this query:

SELECT TOP 36 *
FROM MyTable
WHERE (Column1 = Value1)

It retrieves around 36 rows in 3 minutes. The varchar(max) columns contains in each row 3000 characters.

If I try to retrieve only one row less:

SELECT TOP 35 *
FROM MyTable
WHERE (Column1 = Value1)

Then the query retrieves 35 rows in 0 seconds.

In my clients statistics, Bytes received from server, I have:

95 292 for the query retrieving data in 0 sec

over 200 000 000 for the query retrieving data in 3 min

Do you know does it come from?

EDIT --- Here is my real code:

select top 36 *
from Snapshots
where ExamId = 212

select top 35 *
from Snapshots
where ExamId = 212

EDIT --- More info on clients statistics

The two statistics having a huge variation are:

Bytes received from server : 66 038 Vs More than 2 000 000

TDS packets received from server 30 Vs 11000

Answer 1

Varchar(max) can't be part of a index key and apart from this other major drawback is it cannot be stored internally as a contiguous memory area as they can possibly grow up to 2Gb. So for improve the performance you need to avoid it.