Sudden Performance Drop, Increased Disk Que length

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Sudden Performance Drop, Increased Disk Que length

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 09:18:38

I have SQL Server 2000 SP4, running on Windows 2003 Server dual processor 4GB Memory. Our HR/Payroll database has suddenly started to experience intermittent poor performance, and longer than normal disk que lengths. Nothing has changed significant in the data, when the que lengths go up, there is no particular set of tables, or stored procedures that trigger it. As I performed a DBREINDEX, some of the periods where performance dropped were on idexes that were tiny, (5,000 records or less). and no one else was in the system.

There is a particular employee, who when you went to change his pay details, would bring the server to it knees, and you would have to stop the SQL Server Service. This employees data is identical to several other employees, with no application based violations.

A CheckDB returned no probelms, and DBREINDEX and sp_recompile had no affect on the problem. It seems to be I/O related, but no hardware or software problems present themselves.

Any help would be appreciated.

Thanks,

Brent

It would get so bad, that when you refreshed the current activity it would return a time out error.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-08 : 10:08:45

"who when you went to change his pay details"....maybe stop paying him/her so much

Any chance it's to do with record-locking problems, or blocked SPID's? Can/do you have Profiler running in the background? Is the problem re-creatable? What happens on Saturday, i.e. during low activity, if the problem employee is amended? Have you copied the database to test to check that it isn't an environmental/load problem?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-08 : 10:12:10

You may have an I/O system problem. You can have really bad I/O performance without the system giving you error messages.

You should benchmark disk I/O performance. Unfortunately, it is better if you have a performance benchmark made before you had a problem to compare it to. You may still be able to run benchmarks on this server and compare it to benchmarks from another server with similar hardware.

You should look at the article and discussion from this link:
Article: Benchmarking Disk I/O Performance: Size Matters!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91785

CODO ERGO SUM

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 10:28:43

The Server had no external connections, or other software running when I did the DBReindex, and it would intermittently reduce to about 10 % of normal performance showing extended disk que lengths,
the performance is bad regardless of the employee problem. Tuesday, system was fine, nothing changed, Wednesday performance was way down, with intermittent stops. Connections timing out etc...

quote:
Originally posted by AndrewMurphy

"who when you went to change his pay details"....maybe stop paying him/her so much

Any chance it's to do with record-locking problems, or blocked SPID's? Can/do you have Profiler running in the background? Is the problem re-creatable? What happens on Saturday, i.e. during low activity, if the problem employee is amended? Have you copied the database to test to check that it isn't an environmental/load problem?

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 10:30:11

Thank you, I will go through this process.

quote:
Originally posted by Michael Valentine Jones

You may have an I/O system problem. You can have really bad I/O performance without the system giving you error messages.

You should benchmark disk I/O performance. Unfortunately, it is better if you have a performance benchmark made before you had a problem to compare it to. You may still be able to run benchmarks on this server and compare it to benchmarks from another server with similar hardware.

You should look at the article and discussion from this link:
Article: Benchmarking Disk I/O Performance: Size Matters!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91785

CODO ERGO SUM

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 10:37:25

I manually went through the Disk que length calculations using perfmon, and dividing for the number of spindles, and I had a benchmark on this number of 8 prior to the problem, and now it is close to 20, which is 6 to high based on 7 drives in the server.

So it is higher, but what is driving it higher is these minute or two periods where it spikes drastically.

quote:
Originally posted by Michael Valentine Jones

You may have an I/O system problem. You can have really bad I/O performance without the system giving you error messages.

You should benchmark disk I/O performance. Unfortunately, it is better if you have a performance benchmark made before you had a problem to compare it to. You may still be able to run benchmarks on this server and compare it to benchmarks from another server with similar hardware.

You should look at the article and discussion from this link:
Article: Benchmarking Disk I/O Performance: Size Matters!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91785

CODO ERGO SUM

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 11:34:47

Problem solved.

The Front End Web Server was not resolving DNS, so an internal IP based transaction was creating a locks, and a call using DNS, which was required to finish the transaction was making a large number of un fruitful attempts to finish the transactions, which were not obvious looking at the profiles, because I was not collecting line by line sproc transactions.

Thanks for your suggestions.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-08 : 11:43:33

Simple in the end.....but caustic in it's effect. Now you need to go find out who changed/didn't setup the DNS/Webserver properly....and if a change was involved...hammer their asses!!!!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2007-11-08 : 11:47:11

One step ahead of you, I have alreay sent a nastygram to the Network Admin, and sent network administration documents to his boss, which will cause him a weeks worth of work, which he has neglected.

Thanks,

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 14:03:11

... and send them your expenses sheet for a week in the Bahamas to get over the stress this caused you