Skip to main content
Database Administrators

Return to Question

typo
Source Link
Michael Green
Michael Green
  • 25.1k
  • 13
  • 53
  • 98

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 cnacan 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.

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 cna 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.

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.

Tweeted twitter.com/#!/StackDBAs/status/465199560055586816
Source Link
Sean Long
Sean Long
  • 2.2k
  • 5
  • 23
  • 31

Determining the tables and impact of a large number of SPs

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 cna 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.