April 11, 2003 at 3:02 pm
I have been trying to troubleshoot a performance issue with an application.
There are some application operations that take up to five minutes and occasionally users are getting time outs. Everyone’s first thought was to upgrade the SQL server (Currently duel 550 with 2 gig) to increase performance. But the performance stats on the server appear normal (22% CPU utilization, 400 MG free constantly and Page File % usage is near zero)
I decided to monitor the SQL activity the application creates with SQL profiler. During one of the operations the application generates almost 5000 SQLs.
Here is my question. If I add all the duration field recorded by Profiler, is that an accurate calulation of how long it takes the server to process the SQL statements. If I add all the durations values together they total 10 seconds (10,100 millseconds)
The vendor states that there are some transactions during the operation that I can not seem to find in the performance monitor.
Thanks, B
April 11, 2003 at 3:15 pm
Read following KB articles for trouble shooting of slow query performance.
http://support.microsoft.com/default.aspx?scid=kb;%5Bln%5D;243589
http://support.microsoft.com/default.aspx?scid=kb;en-us;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
April 11, 2003 at 8:49 pm
Thanks for the info, some of it was new to me. Unfortunately I'm look for some more 'real world' references.
I should have put more information in the last email. The database and app are purchased. We do not have any control over the database desing or application design.
What I'm curious about is the reliability of the 'Duration' number assigned to each SQL statement log by the profiler.
After posting this thread, I used qry analyzer to execute all the SQLs recorded by the profiler. The process time was 21 seconds. The profiler's recorded the accumulative duration time as 10. I'm more confident with the result obtained from executing the SQL versus watching it.
Anyone have any input?
April 12, 2003 at 11:21 am
I would contact the software vendor and tell them the list of stored procedures that are taking over 2 seconds. Unless these are massive procs they shouldn't be taking 21 seconds. That is slow. Also, if you are getting timeouts you might want to check for blocks while these operations are executed. You could use sp_who to view the blocks. You also might want to notify the vendor that they may have coding issues because the database is only processing for 21 seconds so the other time is spent in the code. Although, if this sometimes only takes 5 seconds then it might be the database.
Darren
Darren
April 12, 2003 at 12:44 pm
It generates 5000 db requests? Almost no way to make that fast, just the overhead of 5000 network calls is going to slow it down. I think duration is pretty accurate, but its only raw processing time, that's not going to show (as far as I know) the additional time required to stream it back to the client and process.
I agree that monitoring for blocking makes sense. If you can spot one, then you can jump to the bad sql - bad as in maybe you need better index, different access, etc.
Even without that, have you looked at the ones with the highest duration, cpu, reads, to see if you have opportunity for tuning there?
Andy
April 15, 2003 at 7:28 am
Thanks for the info on the duration filed. That makes sense. If I run all the SQLs records in qry analyzer, it takes roughly 20-30 seconds.
I’m going to send the results to the vendor and have then take a look. In their defense, their app does do some rather complicated things, which explains the 5000 SQL calls. Though, every time I type ‘5000’ I shudder.
My immediate goal it to determine where I should invest hardware and time. Even though the SQL server appears to be performing adequately (good stats and 20 seconds to process the SQLs) I’m recommending buying a new server. Think of the SQL activity if a couple users run the same function at once.
But, in the interim, I’m going to start monitoring the Citrix servers that host the client. Poor Citrix performance could explain why the SQL server processes the request in 20-30 seconds and when the users run the same operation from the client it takes 5 minutes. I’m going to look at the network between the Citrix server and SQL server too. I believe it is currently 100MB.
Thanks All!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply