Re: Single-Threading / Performance issues
From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 10/28/04
- Next message: Ryan Breakspear: "Deleting Fields"
- Previous message: Tom Moreau: "Re: Delete without log ??"
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 28 Oct 2004 09:40:46 -0400
how are you checking waittypes exactly?
If you are having these timeouts.... then I suspect that you must be having
a varitey of waits being set. Are you using dbcc sqlperf(waitstats) or just
looking in sysprocesses? sysprocesses is transient and sometimes it's hard
to see what's really happening re: waits?
Also, I suspect the stats issue is important as suggested in other threads.
Also... you say 'when a long select is running' it the 'other www pages
timeout'.
Are you seeing any cxpacket waits at all? It's possible that you have
queries going parallel that are causing performance degradtion. As a test...
you might want to try
sp_configure 'max degree of parallelism', 1
reconfigure
to disable parallel queries and see if the problem goes away.
-- Brian Moran Principal Mentor Solid Quality Learning SQL Server MVP http://www.solidqualitylearning.com "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: Ryan Breakspear: "Deleting Fields"
- Previous message: Tom Moreau: "Re: Delete without log ??"
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
