Single-Threading / Performance issues
From: Peter The Spate (anonymous_at_discussions.microsoft.com)
Date: 10/28/04
- Next message: Rect: "updlock"
- Previous message: Hubert Mayr: "sqlserve 2000 odbc error"
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Next in thread: Dan Guzman: "Re: Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 28 Oct 2004 03:59:21 -0700
Hi Robin
Firstly have a look at the following
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/instsql/in_upgrade_5jw3.asp
The reason why I put this in is because you said you
restored the database, so your statistics are going to be
wrong. If you supply an email address I can send you some
code that will update the stats.
Other things you can look at.
In the database properties take off auto close and auto
shrink if its on, and make sure the compatability level is
set to 8.0
Under the server properties select Dynamically configure
SQL Memory, under processor control make sure that all
cpu's have bee selected, select use all available
processors.
Anyway there are a lot more options you can use as well.
Other things you can look are server stuff. Find out what
applications are running on your server, for instance
having exchange on the same server will really effect
performance. Set up a performance monitor and check what
sort of performance your server has, and if any of them
look high then speak to the server guy.
In the meanwhile get the book SQL Server 2000 Performance
Tuning.
As I said, if you give me your email I can provide some
code.
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
>-----Original Message-----
>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: Rect: "updlock"
- Previous message: Hubert Mayr: "sqlserve 2000 odbc error"
- In reply to: Robin Martin via SQLMonster.com: "Single-Threading / Performance issues"
- Next in thread: Dan Guzman: "Re: Single-Threading / Performance issues"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
