August 31, 2010 at 12:19 pm
I'm getting a lot of complaints about slow response from the web users. I was blaming all the sql jobs that run every 5 min to bring data from external systems. To prove it, I ran a trace with the sql jobs on and another with the sql jobs off. Unfortunately, I see not much difference in Duration, Reads, Writes, and CPU time. It makes sense that it will take exactly same amount of resource no matter how busy or idle the sql server is. However, I thought at least the duration time should get shortened. Am I a dumb DBA?
August 31, 2010 at 12:30 pm
That would tell me that the additional overhead from the jobs were not impacting the web server requests. Compare the web request duration from the trace to the percieved duration from the user's perspective. It may be that the lag is not the databae server. If you really think it is the DB server, I would look at the requests themselves and see why they are taking so long. Look at the queries, execution plans, index strategies, etc.
August 31, 2010 at 1:14 pm
Hi John,
Additional question. Was the Duration column a right place to look into when comparing performance? Is there any other columns that could tell me the difference? It seems like Duration, Reads, and CPU columns are only useful for figuring out the costly queries, not the comparison.
Thank you again.
August 31, 2010 at 1:48 pm
Well, yes and no. The duration of a given call can change based on other server load so you could say that a lower duration when the jobs are turned off means that the jobs were causing contention, but that is not a real accurate measure of contention. I would that if you ran the same web-server call 10 times in a row, you'd see a variance in the durations.
If I was suspicious of a SQL call taking too long, I would first look at that call and see how it performs from Management Studios on a non-busy system. I'd get an execution plan and also IO statistics (from SET STATISTICS IO ON). This should give you some sort of expectation for how that call should perform. Then, if the call is not performing close to this in a busy production environment, start looking for other processes that access the same objects (a SQL trace can tell you this) at the same time and look for blocking. If you find blocking, look at tuning the performance of the blocking job and maybe even the blocked job.
August 31, 2010 at 4:15 pm
Thank you John.
September 1, 2010 at 8:11 am
No problem. If you find a particular piece of code that you need to tune, or 2 processes that aren't playing nice together, feel free to post the code (as well as the table/index defs....see article in my sig line for tips on this) as we can help look into it.
September 1, 2010 at 10:16 am
Definitely I will. Thank you so much.
September 2, 2010 at 1:55 pm
Profiler results (Reads, Writes, CPU, Duration) are superior to STATISTICS IO results; they're more comprehensive.
CPU or Reads+Writes being more important depends a lot; are you IO constrained (perfmon avg sec/Read avg sec/Write is high) vs CPU constrained?
September 2, 2010 at 6:27 pm
When I also ran Perfmon twice; one time with the sql jobs on and the other with the jobs off. I saw huge difference in I/O Disk Queue. The queue was staying consistently high with sql jobs on. It makes sense because the sql jobs are importing data from external systems. cpu and memory wasn't big difference.
September 2, 2010 at 9:14 pm
High disk queue length can be a sign of a disk bottleneck. This depends on your disk configution, # of disks, and queue length value. Search SSC for disk queue length and you'll get a bunch of help. Here's just one example:
http://qa.sqlservercentral.com/Forums/Topic930859-146-1.aspx
This could be a disk performance issue or maybe you'll want to tune your import process.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply