May 19, 2009 at 10:39 pm
Hi Everyone,
I'm running into a performance issue on our SQL Server 2005 environment standard edition whereby we are getting very erratic performance results. Simple queries that normally run under 20 seconds now take 5 times that amount of time and this across all databases. Checking disk utilization, network and cpu shows nothing out of the ordinary. I do seem to be getting more processes in suspended state however. Running the usual performance analysis on the server I can't see anything out of the ordinary happening. There isn't any heavy activity that I can identify. The only common factor seems to be a Visual Basic 6 debugging session taking place.
My question to you, is it possible for a VB6 debug session to cause havoc on performance?
Thanks
Robert
May 19, 2009 at 10:44 pm
I could be holding open transactions waiting for the developer/engineer using the debugger, which could in turn be blocking other users on the server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2009 at 10:51 pm
Yeah, that was one of my initial concerns and I checked for blocked processes but nothing showed up. Checked also for memory consumption and the server has enough internal memory to cope with any of the requests. No other processes were running (or I should say seem to be) and causing any contention issues either.
May 19, 2009 at 11:53 pm
My rule of thumb for tracking down performance latencies in Windows environments: if it's slow, but it doesn't appear to be waiting for anything, then it's probably waiting for something out on the network.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2009 at 2:41 pm
VB6 is not the root of the problem. It might be anything from HBA card problem to a new job that runs every 5 sec.
From my personal experience: 20 sec. for one query looks suspicious. Are you sure there is no problem with these queries? For example, you are not waiting for user respond inside open transaction, aren’t you?
Alex Prusakov
May 20, 2009 at 4:30 pm
Hi Alexander, no there are no waits. Just a plain batch job query, there are no internal lockouts either. Looking at the running processes on Windows server 70% idle time and the rest taken up up SQL, with the odd skip into for other processes. Network activity is minimal and show no spikes. On the dual quad CPU front only 3 of the cpu's peak out.
What makes the issue more diffiucult to analyze is that it is very intermittent and while the analysis is taking place all of a sudden the issue goes away. Very frustrating.
May 20, 2009 at 10:23 pm
I would recommend to set up perfmon and profiler monitor for queries that run over 20 sec. (that was the time you’ve mentioned before). Then join results in the profiler and try to find out what is affecting performance. There is no magic in applications – look close and you will be able to find the cause. You need only figure out how to repeat situation at any time so it would be possible to investigate it. Good luck. Worst case scenario – hire consultant to do research and discover.
Alex Prusakov
May 20, 2009 at 10:30 pm
Yeah, my thoughts exactly. It's going to be the long way round, the problem I'm really having now is it's very erratic and as I'm unable to pinpoint more or less where the problem is finding it difficult to duplicate and capture the analytics around it. Sigh....the life of the DBA...still when I do duplicate it I enjoy analyzing the problem...just hate the wait in between.
Thanks guys. Will let you know the result when I find out what it is.
Cheers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply