Re: SQL Server 200 on 4-proc Xeon only using one proc?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 07/06/04 

Date: Wed, 7 Jul 2004 09:44:02 +1000

There would be a variety of potential causes for this type of behaviour, but
something as simple as an inefficient query could cause this. Consider a
single run-away query that's consuming a large number of rows, potentially
scanning a table or hasing a join etc. Whilst it's consuming the CPU it's
also holding locks which are blocking the work which is scheduled onto the
other threads on other CPUs. When the run-away query eventually completes
its work, it's locks are released & the other threads can get back to work.
Assuming they're scheduled onto the other CPUs, this would then explain
those CPUs launching into heavy CPU utilisation once the run-away query
completed.

Have you checked that you don't have some large inefficient query running
during the period of increased page response time?

One way to do this is to use the SQL Profiler to identify TSQL statement
completed or stored proc completed events with a filter on reads or
duration.

Another way is to identify the executing thread on the 100% CPU, obtain it's
ThreadID from the Windows Performance Monitor & convert it back to a
specific SQL connection via the master database's sysprocesses table. Once
you've got the spid, you can monitor what it's doing at a more fine grained
level. sp_who2 might also be a simple way to get the sql connection. Either
way, once you've got the sql connection (spid), you can analyse what work is
actually being performed / locks taken etc. Another option is to capture run
time execution plans from the Profiler as well. These are very brief points
but post back if you'd like any of them expanded upon.

HTH

Regards,
Greg Linwood
SQL Server MVP

"R David Francis" <rdfozz@pobox.com> wrote in message
news:94abccfa.0407061514.5695fd14@posting.google.com...
> I am running SQL Server 2000 on a Winodws 2000 box (4 Xeon processors,
> with hyperthreading - therefore, eight processors from the system's
> perspective). We recently upgraded to SP3a (thought we were already
> there, but our hosting provider had evidently not understood our
> directions). The database serves a web application.
>
> Today, under what appeared to be a somewhat heavier than usual load on
> the web side, we saw increasing page response times on our web
> servers. An attempt to check for a blocking lock condition on the
> database in question resulted in a hung copy of Enterprise Manager.
> We connected to the server itself remotely, and checked Task Manager
> to see how loaded down we were.
>
> We were surprised to see that one of the processors was maxed out, and
> all the others were essentially idle. After a few minutes, we noticed
> a second processor start to build a load; however, rather than
> building to the point both processors were under a 50% load, the one
> that had been busy droppped to nothing, and the one that had been free
> shot up to 100% usage. During load testing a couple of months ago, we
> had seen activity split evenly across all eight processors. The only
> major change since then was the SP3a install.
>
> I have checked our settings - we have "Use all available processors"
> on and we appear to be able to see all eight processors.
>
> In the back of my mind I remember seeing an article that covered our
> situation to a tee; unfortunately, I cannot recall where I saw it, nor
> can I find it now.
>
> Does this situation seem familiar to anyone? A box that correctly
> used mutliple processors before a service pack upgrade only using one
> for SQL Server 2000 afterward.
>
> If you've got a pointer, I'd be grateful.

Relevant Pages

  • Re: Slow form, again
    ... subforms if you think the problem might lie there. ... Is the below query what the Parent form is based on? ... Removing all subforms gets the form to load in about 15 seconds. ... If it's not my database I'm not sure what to tell my co-workers and boss ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Slow form, again
    ... Thanks everyone for the posts. ... It is over a LAN and the form is bound to a query. ... Removing all subforms gets the form to load in about 15 seconds. ... database, and same brick wall! ...
    (microsoft.public.access.tablesdbdesign)
  • Re: To load or not to load
    ... First, write a query like ... >it should be 1 and therefore load. ... what you are looking for, chr, is not in this string... ... shouldn't the DCount solution work - you are right, ...
    (microsoft.public.access.formscoding)
  • User Input in OnLoad Form Event
    ... The second form holds data fed from a query, ... The second form loads from the query. ... The list boxes in the second form requery on load. ...
    (microsoft.public.access.modulesdaovba)
  • Re: FIELD CALCULATION
    ... Create a query based on tblLoadLeg. ... Create a main form bound to tblLoad. ... There's probably much more than this to it, and there are better ways of handling the mileage for each leg (so the database automatically uses the last HubEnd and LocEnd without having to enter them again), but that should get you started. ... If you have one load number that represents the entire distance, ...
    (microsoft.public.access.forms)