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.

2 Answers 2


If you know for sure that these 35 programmables are the culprits a combination of Profile/Events for elapsed time and cached plans, plus sys.dm_exec_query_stats should get you a long way to understanding where about the pain lies.

@Paul's comment about recording a baseline is important, though.

  • Ah-ha! That's the DMV I'm looking for. That will help out a lot. Paul's comment is a fantastic outline and something I agree that should be a more usual approach, but I'm trying to do a very specific thing right now. It's not really a general tuning project, it's trying to figure out why this specific process regressed, and out of the myriad tables it touches which should be left uncompressed. So the DMV with the query plans will let me see what has the most full table scans.
    – Sean Long
    Commented May 12, 2014 at 11:56

A scientific approach is based on routinely collecting process and procedure performance information, either using a commercial package or a more home-grown solution. You could also start completely from scratch collecting information from Profiler or Extended Events. The important thing is to capture data regularly, and to make it easily consumable (e.g. using SSRS).

Usable historical information makes it easy to track gradual performance changes over time, anticipate increased resource requirements before they occur, diagnose sudden changes, and identify and test areas for improvement.

On that latter point, I believe your approach should be something like this:

  1. Establish good baseline information over a reasonable period of time
  2. Identify areas that might benefit from compression
  3. Test both ROW andPAGE compression in the improvement target area
  4. Test aggregate changes on a representative workload
  5. Refine or regress changes that were not beneficial
  6. Implement the change and monitor the effects in production

This is a lot easier than attempting to track down performance regressions in complex code after the fact, without a baseline to compare with.

  • I completely agree, and that approach is definitely something we're building towards. The trouble I'm having now is a perfect example of that: tracking down performance regressions without historic data is a pain. Right now, though, I'm in the middle of an investigation around page compression and am trying to dive into the code to determine why somethings are regressing why others aren't.
    – Sean Long
    Commented May 12, 2014 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.