I'm trying to track down what is causing some response times to increase when data is compressed by page compression, when the system is not CPU bound and 95% of responses see a decrease in response time.
So what I did is track it down to a single process happening in the system, and I profiled that process to determine what SPs and UFNs are fired off during the process. My original thought was that I could run each SP and UFN in isolation and take a look at the query plans to see where full scans are happening, this might require the data to be unpacked from the compression and might cause a wait to fire.
So what I have right now is:
- A list of SPs with the parameters used
- The database that the SPs are run on
- Unfettered administrator access to the system to reproduce the issue
- A profile trace of the process in question
Since there's something like 35 SPs/UFNs I'd have to sort through, I'm wondering what the most efficient way is to narrow down the cause. I can infer that some SPs are more likely culprits than others from my experience with the system, but I'd like to try to narrow it down in a more scientific way. Are there any tools or methodologies that might help me figure out the most likely offenders?
If I can determine the objects that are slower when compressed than when not, this is going to help inform our strategy around page compression.