September 24, 2025 at 12:00 am
Comments posted to this topic are about the item When Page Prefetching Takes a Back Seat – Exploring Trace Flag 652 in SQL Server
September 24, 2025 at 1:04 pm
I am replying to this article because I ran into an issue which ultimately was found to be SQL Server overloading our storage subsystem and the resolution was to turn off PRE-FETCH PERMANENTLY.
You read that right. I have a SQL Server OLTP system where I regularly hit 100k+ I/OPS sustained for 15-30 minutes, sometimes longer when ETL operations are needed during normal working hours.
The subsystem, which I will leave nameless for the purposes of this reply, employed a caching system that among other things would de-duplicate the data before finalizing the write(s) to disk. Mind you DELETES from SQL server are WRITES to a disk subsystem.
Our SQL server system was "writing" over 100k IOPS sustained (sometimes hitting double or triple those numbers) for its normal work and it also had several (5 at one point) AG secondaries being created with disks/mount points all pointing to the same back-end disk array.
A problem was not manifested in SQL per say, there were times of I/O waits, but the main problem was with the disk array itself.
I got a report from one of the disk admins that the caching system was being bypassed to dump data directly to the flash disks. This was using up more FLASH storage space than expected because of the de-dupe bypass.
Upon checking disk metrics in SQL, I noticed something peculiar. Pre-fetch stats and I/O metrics were EXACTLY the same as normal disk operations. This seemed odd as you would expect the metrics to be an inverse curve at best (as the pre-fetch data was loaded and IO increased for pre-fetch the main I/O would reduce at an equal amount) or at least be differing when one hits peak the other should be sub-peak. But they were exact. I took this to mean the I/O work was being duplicated.
After several hours of trying to figure out why this was happening, I came across the online documentation for this trace flag 652. It was related to pre-fetch data, and I could enable/disable it basically at will.
Given that my investigation did not reveal a memory issue or an OS disk threading issue, I decided to turn off pre-fetch for the Primary (AG) SQL server system. Which is also the main source of "writes" to the disk sub-system.
This had a very noticeable effect on the FLASH array storage sub-system. Their queue requests were reduced significantly. But it still wasn't enough to prevent the disk subsystem queue from taking too long to process and it was still dumping to disk.
So, I turned off pre-fetch to all secondaries as well and that was enough to allow the caching system to catch up after 24-48 hours.
After monitoring and more checks by not only me but our storage team we determined that putting multiple Availability groups on 1 flash array was bad, not only did it eliminate the HA aspect of the AG's (if the array went down all the AGs went down with it) but snapshot timing was also found to need adjustment.
Once all adjustments were made, I was left with one last step. re-enable pre-fetch. I decided to wait to collect metrics and see if system performance would actually suffer with it off.
it actually did not suffer. The IO waits were essentially eliminated, and the disk subsystem was now optimized for the type of SQL Server workloads going through it.
After a month of collecting metrics and keeping an eye on the system I found only 2 seconds of I/O waits over a 30 day period and found that to be an acceptable range. 4 years later no prefetch and no disk subsystem issues.
Working this problem to our team's eventual solution/conclusion was one of the greatest joys I had working as a SQL Server DBA. I helped solve a problem with a system SQL relies on but couldn't really control or administrate. In my mind it was an achievement.
I apologize for length of this reply, but it is my hope that in the future at least 1 person will find this writeup and it will help them solve an issue they may not even knew they had.
Respectfully submitted,
Greg R. DBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply