Decrypting object performance (batch DBCC PAGE calls)
I frequently need to "generate scripts folder" against large (10k+ objects) databases including encrypted objects (thousands of encrypted stored procs).
A recent run on a (50ms rtt) vpn link took 2+ hours to complete.
It appears that object decryption performance appears to be bottlenecked on
performing 3 round trips to read a single db page, specifically:
batch 1: dbcc traceOn(3604)
batch 2: dbcc page(DB, 1, 6573692, 2)
batch 3: dbcc traceOff(3604)
batch 4: dbcc traceOn(3604)
batch 5: dbcc page(DB, 1, 6573694, 2)
batch 6: dbcc traceOff(3604)
I think batching page reads (and eliminating many traceon/traceoff commands) would make object decryption performance much better, especially over higher-latency links:
batch 1:
dbcc traceOn(3604)
dbcc page(DB, 1, 101882422, 2)
dbcc page(DB, 1, 101882423, 2)
dbcc page(DB, 1, 101882496, 2)
dbcc page(DB, 1, 101882497, 2)
dbcc page(DB, 1, 101882498, 2)
dbcc page(DB, 1, 101882500, 2)
dbcc page(DB, 1, 101882501, 2)
dbcc page(DB, 1, 106085028, 2)
dbcc page(DB, 1, 105837684, 2)
dbcc page(DB, 1, 114755183, 2)
dbcc traceOff(3604)
Attached is an annotated pcap screenshot showing the 3x RTT latency per page read.