DBCC TRACEON (652) does not disable read ahead reads

Refering to this StackOverflow question: https://stackoverflow.com/questions/13307485/sql-server-clearing-cache-has-no-effect-on-io-stats

I used the trace flag 652 which had, at the time disabled the read ahead function properly.

I came back to it, and it no longer does so.

Query:

DBCC DROPCLEANBUFFERS
DBCC TRACEON(652,-1)
select a.JDID,a.JDDESC from t1 a with (Index = ccsi) JOIN t2 b on a.manager = b.JDID JOIN t3 c on a.department = c.jdid

Stats:

Table 't1'. Scan count 4, logical reads 5884, physical reads 364, read-ahead reads 13679, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution plan:

```

Trace flag 652 generally does still work to disable read-ahead (tested on 2012 SP1 CU1 - build 11.0.3321). The exception to this is for hash joins using Batch Hash Table Build, in which case the columnstore index feeding the probe side of the join still uses read-ahead regardless of TF 652:

The diagram above shows the XML show plan output from the question, loaded in SQL Sentry Plan Explorer. I have highlighted the Batch Hash Table Build, Hash Match Inner Join, and probe-side Columnstore Index Scan plan elements necessary to defeat TF 652.

Trace flag 652 is effective on all other column-store plans I have tried (both row and batch mode processing) which suggests that this is either an oversight, or intentional because this specific execution path works so differently internally.

Trace flag 652 is only very minimally documented so I am not sure that support would be available for it. Nevertheless, this issue might be worth reporting on Microsoft's feedback site as a suggestion (I do not believe it qualifies as a bug) for enhancement in a future hotfix, service pack, or full release.

StackExchange Database Administrators Q#29333, answer score: 10