Determining the tables and impact of a large number of SPs

Question

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.

Answer 1

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.

Answer 2

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.