PROBLEM: Sporadic spikes in Avg. Disk Queue Length - PLEASE HELP

[elitecobra]

PROBLEM: Sporadic spikes in Avg. Disk Queue Length with users freezing up until it comes down.

Server:
-SQL Server 2000 SP4
-Windows Server 2003
-4 Intel Xeon Processors 5130 @ 2.00 GHz
-4 GB of RAM (SQL Configured Dynamically with min. of 0 and max. of 3072)
-Max Worker Threads – 255

Environment:
-100 users with about 65 on Thin Clients going through a Remote Desktop Server
-Front End CRM package is a program called Telescript
-I do not use replication or any triggers, mainly just scheduled DTS packages
-Largest and most heavily used Database is 13Gbs with using a fill factor of 80 on my indexes

Recent Changes – The most notable thing that I changed in the last couple of weeks as this problem arose was the SQL Mail function. I started using that and created some DTS packages that sent out emails. Our user base has been steadily growing, but nothing drastic.

So Far – The buffer cache hit ratio is good and I have not seen any problems with memory so that is ruled out. The processor time never maxes out, but it does appear to double when the Avg. Disk Queue Length spikes. I re-indexed my active tables and performed shrinks over the weekend, but the problem occurred yesterday again. Sometimes it will last under a minute, but the problem is really when it lasts for several minutes. I used SQL Profiler to try and identify what the query was causing the problem, and while I see some large audit times, I cannot tell what the problem is. I had a hard time reading through that with all of the Stored Procedures that were invoked (ie. cursor fetches). The server itself is very new and has only been in use for about 2 months, so I couldn’t imagine it would be hardware fragmentation.

QUESTION: What else should I be looking at or could I use to figure out where this problem is stemming from? I am in a corner and could use any suggestions as to diagnose this problem.

Thanks in advance,

Don
dkoehne@401kexchange.com

[skhanal]

One way to find out is disabling SQL Mail and see if it resolves the issue.

While you encounter high disk queue, check lastwaittype column in sysprocesses table. This might tell you what kind of wait the server is experiencing.

You can refer to this KB article for possible solutions

http://support.microsoft.com/default...b;en-us;822101

[rmiao]

Also check page split count and disk r/w bytes in perfmon. By the way, how many disk arrays does the server have? How do you place db files on the server?

[elitecobra]

What am I looking for in terms of numbers for these counters in perfmon?

5 Disks in the array - mirrored from what I was told.

Maybe the question is just that simple, but I don't understand what you mean by "How do you place db files on the server?"

[elitecobra]

What about increasing the Max Worker Threads?

I understand that increasing it takes more overhead from the server, but in looking at User Connections v. Threads - User Connections is consistently higher. Could this be the bottleneck? Is there anything else that I can look at to confirm this?

[rmiao]

Don't need increase work threads since you have only 100 users.

Does server have only in disk array? To reduce disk contention, should put tempdb, db data files and db log files on separate disk arrays. That's what I mean place db files.

[elitecobra]

I get what you are saying. Yes, they are all on the same partition. However, I am not a hardware guy, so I am not sure if this is right or not, but I believe there is only one storage array. Or is each partition considered their own storage array?

As far as worker threads go, I have read opinions on both sides of what to do there. I thought "User Connections" mattered more than actual users in that case. My "User Connections" far exceed the number of users.

Thanks and sorry for the dumb questions.

[rmiao]

Partitions on same array don't give you performance gain, split the array to multiple smaller ones if possible. It'll be better if can get multiple disk channels.

How many user connections you have? Have more than 255 concurrent active connections constantly? If not, don't touch worker threads. You can check that with 'sp_who2 active'.

[elitecobra]

The concurrent active connections is hovering around 20, but probably higher during peak times. Thanks for the info, that eliminates one more candidate.

I will talk to my System Admin about the partition, but do you know if it matters that I have the TEMPDB along with all other databases set up to "Automatically grow file" "By percent" (10) with "Unrestricted file growth". The only reason I ask is because I installed Idera SQLCheck and the section for "Database Allocation" is at 91% full and it recommends 75%. I am not sure how this is a problem with Automatic file growth, but it was another flag to check out.

[rmiao]

Tempdb should be on its own disk array anyway, you can set larger initial size for it to reduce number of growth.

[elitecobra]

I will move the TEMPDB over the weekend.

Another question - should I also consider moving the Transaction Logs for my DBs to another array as well?

Have you ever used "RamDisk" software? If so, is it something worth considering?

[rmiao]

Yes, should separate from data files.

I don't know that, but any software disk array solution doesn't help.

[elitecobra]

I moved the tempdb and the log files to a separate array. While things seemed to have improved remaining fairly quiet yesterday, we had some big lock ups today (07/24/07). It appears to be something that users are doing. One such instance I came across was a user searching for a record in a 1.4 million record database on an indexed field, but instead of putting in something to search for he left it blank. This is a third party software and there is not much I could do to prevent that.

There are other things happening, but despite the use of SP_WHO2 ACTIVE, I can't point at a query or a user for that matter. We are using a Terminal Server and our thin clients all show as coming through that server. I am using perfmon and profiler and am at loss for identifying the query in progress.

Anymore advice is appreciated?

[rmiao]

Do they use same windows account to logon Terminal Server? Try add ntusername in profiler so you know which use ran which statement.

[elitecobra]

NTUSERNAME is empty. SA is used as the authentication for the ODBC connection that connects them to SQL. Everything comes through with "SA" as login and the host being the Terminal Server.