Re: Processing queries simultaneously

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

From: Paul fpvt2 (Paulfpvt2_at_discussions.microsoft.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 09:59:03 -0800

Thank you for your reply.
> Having another app such as this on the same server is never good for
> performance since they will both compete for resources, especially memory
> and cpu. This is also a likely cause for the performance issues. Having the
> app on the same machine does not mean you are not using the network for the
> communication.
Did you mean in this case it might be better if I have the SQL Server in its
own machine, and the 2 VB applications (1 to store the data and another to
retrieve the data) in another machine ? This SQL Server machine has 2 gig of
memory. When the VB app that retrieves the data returns 500,000K records, it
uses about 50 meg of memory, but after it's done sending to the client, it
releases that memory again.

>You mentioned it doesn't use a lot of cpu so you are most likely constrained by >some other resource and using a single proc vs. two may not be much of a >difference.
After investigating further, I can see that if the query returns few records
like 10,000 records, the CPU usage is not that much (5% range). But, if the
query returns 500,000K records, it uses quite a bit CPU (in the 30-40% range)

Thanks.

"Andrew J. Kelly" wrote:

> >>You do but not for this expensive query. Add a hint to the query of
> >>MAXDOP
> >>= 1 to these big queries so they only use one processor and leave the
> >>other
> >>for the other users.
> > If I force the query to use one processor, will it slow down this query ?
>
> That depends on what other bottlenecks there are. You mentioned it doesn't
> use a lot of cpu so you are most likely constrained by some other resource
> and using a single proc vs. two may not be much of a difference. The only
> way to be sure is to test. But if you want to ensure there is always a
> chance of another proc being available for other users this is the way to do
> it. It's usually a trade off.
>
> > What I meant was from the VB program, I call the stored procedure to get
> > all
> > the records. Then when the total number of characters of the data is >=
> > 1000
> > (c_MAXOUT), I send the data to the client.
>
> Having another app such as this on the same server is never good for
> performance since they will both compete for resources, especially memory
> and cpu. This is also a likely cause for the performance issues. Having the
> app on the same machine does not mean you are not using the network for the
> communication.
>
> --
> Andrew J. Kelly SQL MVP
>
>
>
>

Relevant Pages

  • Re: for linux, which CPUs are almost always faster than which other CPUs?
    ... Is there any way to find out how to compare two different CPUs for the ... For a *web server*, the main bottleneck is you disk drives / mass storage ... The *second* bottleneck will be memory. ... And then I'll ask someone about comparing CPU A and CPU B, ...
    (comp.os.linux.misc)
  • Re: 64-bit memory consumption
    ... CPU cores and memory keep pace with each other, ... There is time spent in GC that won't be affected only to the GC cycles, you get indirect hits in the form of poor cache coherency and cache pollution in the code that uses GC'ed memory. ... Desktop CPUs with significantly larger caches aren't there yet, and on a server, your cache is shared by multiple applications, and you can't appropriate it all for your application's purpose without affecting the rest. ...
    (borland.public.delphi.non-technical)
  • Re: Slowly Terminal Server response
    ... physical memory is an indicator of available memory. ... If Task Manager only shows a single CPU graph, then only one CPU is installed. ... A good general guide to keep in mind is that most applications require the similar amount of memory as they would on a desktop for each user. ... I normally start with a general guideline of 25-30 concurrent users for a server with 4 GB of RAM and 2 CPUs and then do testing for a specific set of required applications. ...
    (microsoft.public.windows.terminal_services)
  • Re: CTRL-ALT-DEL SCREEN VANISHES
    ... for memory and CPU can give you an idea of what is going on. ... The server runs the following apps/services ... force the problem to occur making it more difficult to troubleshoot. ...
    (microsoft.public.windows.server.security)
  • Re: Slowly Terminal Server response
    ... You can use threadmater to cap the cpu usage on your access app: ... How much memory is left when the 20 sessions are running? ... MVP - Terminal Server ...
    (microsoft.public.windows.terminal_services)