Re: Single-Threading / Performance issues
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/28/04
- Next message: Wayne Snyder: "Re: Consequences of Error 644?"
- Previous message: Wayne Snyder: "Re: Cannot change login associated with DBO for MasterDB."
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Next in thread: Tony Rogerson: "Re: Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 28 Oct 2004 06:59:26 -0500
> 2x 72Gb disks. (apparently set to RAID-5)
Apparently not since at least 3 physical disks are required for RAID-5.
> I've seen other funnies as well ... when a single user runs a long query
> (Table scan of some 8 million rows) ... PerfMon shows 100% disk
> utilization.
This is normal but the percent disk utilization metric isn't much use
anyway. Monitor the avg. disk queue length. This should be no more than 2
times the number of physical disks in the logical volume.
Check memory counters to make sure you are not doing physical paging. You
might want to set SQL Server 'max server memory' if you have other apps
running on the box.
Ensure you have log files on separate physical disks. This can
significantly improve performance of write-intensive applications.
-- Hope this helps. Dan Guzman SQL Server MVP "Robin Martin via SQLMonster.com" <forum@SQLMonster.com> wrote in message news:e7b8cec0bd5a4998b82fe3c64876ea5c@SQLMonster.com... > Hello World ;-)) > > I'm not a DBA, more of a programmer, so am calling on you good folk for > clues - because I'm at my wits end. > Apologies up front for my long post - I'm hoping to give sufficient > indication of what's going on. > > > Early this year my database was migrated from a single CPU, SQL7 database > on Win Nt4 to a quad-processor, SQL2000 on a Win2K platform. > (Done by restore, not DTS of data) > > Trouble is the performance is not what it should be ... worse than on the > old system ... it appears to me as if the system is single-threading at > times. (when a long select query is running, the website users will > time-out trying to log-in). > > During this time, refreshing under Enterprise Manager to check "Current > Activity" gives you ... hourglass. > No update until whatever process it is has completed enough to let you in > to look. > > I haven't found any blockers, and the wait-state codes (when I get in to > ask the question), show nothing wrong (not waiting), or the odd > disk-related wait state. (I can understand waiting for disk - gotta fetch > the data from somewhere ;-)) > > > My DBA has tried tweaking some parameters and sez nothing is wrong, my > server guy also sez it's not his problem. > > So I'm left in the middle trying to figure out what's going wrong. > > > > > Here's an example. > From Query Analyser, I run my import proc - which first copies data > across the network and then loads to SQL. > Seems to be going fine until nearing the end of the proc - about when the > load happens. > > Then Enterprise Manager hangs on Refresh of "Current Activity". > I alt-tab to WinVNC session and try get in to start PerfMon. > Connect okay, enter password, press Enter ... Nothing happens. > Can't alt-tab back to EM - not responding. > > Alt-Tab to Query Analyser - proc has completed ??!! > > Still no response on EM or WinVNC. > > Some two minutes later - both come back. > Nothing waiting, no apparent problem. > > What the heck is going on ... some sort of SQL cleanup ? > > > > Here's some other detail :- > > The new hardware should be fine ... > HP/Compaq branded server, > 4 Intel Xeon 2.5 gHz processors, > 2048Mb memory, (maybe too little ?) > 2x 72Gb disks. (apparently set to RAID-5) > > > Product: SQL Server Standard Edition. > O/S: MS WinNT 5 (2195) (SP4) > Version: 8.00.760 (SP3) > > Memory Tab: > Dynamically configure > No reserve > > Processor Tab: > SQL to use all 4 processors > Max worker threads 100 > Use all available processors > Minimum Query plan threshold: 5 > > > > > The main .MDF is around 45Gb, so shouldn't be a problem in terms of size. > We have, however, been battling to back this thing up, now having to > reboot daily because it gets locked/held ... system message about "another > process" has locked a portion of the file. We've lost the database twice > on cancelling a running Select query - so we don't do that any more. > > > > I've seen other funnies as well ... when a single user runs a long query > (Table scan of some 8 million rows) ... PerfMon shows 100% disk > utilisation. > At this point access to the O/S facilities itself are slowed / frozen ... > and appear to wait until the query completes. > (CPU etc almost zero, screen-paint of forms delayed !) > > Occasionally I get this in the Event Log ... > Error: 17883, Severity: 1, State: 0 > The Scheduler 0 appears to be hung. SPID 6, ECID 0, UMS Context 0x039634A0 > > This happens at the time data is flowing / batch updates of incoming > data records. > > > Sometimes a BCP of data into the database will die, copies across the > network are fine, but it fails on the import step. > Not always at the same time or on the same table - and this is circa 1 AM > with nothing else supposed to be running. > > > Another thing. The first time I run a long table-scan, it takes ages - > like the database is sleeping / paged out. > If I then change the selection for a subsequent scan, the results are > almost immediate. > (Within reason of course - too different and it's just as slow - like 20 > minutes to scan 8 million rows) > > > Based on my years in the IBM mainframe world, this smacks of an I/O > bottleneck of some sort. > My guess is disk vs memory, but sadly, I'm going to have to prove what > the problem is before my server guy will respond. > > > ANY CLUES would be most welcome > > Regards, > Robin Martin. > > System Integration and MIS > Nedcor Call Center > +27 11 710-3040 > > NATMTWCBEBI - Never ascribe to malice, that which can be explained by > incompetence. [Napoleon] > > ***************************************** > * This message was posted via http://www.sqlmonster.com > * > * Report spam or abuse by clicking the following URL: > * > http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=e7b8cec0bd5a4998b82fe3c64876ea5c > *****************************************
- Next message: Wayne Snyder: "Re: Consequences of Error 644?"
- Previous message: Wayne Snyder: "Re: Cannot change login associated with DBO for MasterDB."
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Next in thread: Tony Rogerson: "Re: Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
