Re: Processing queries simultaneously
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/29/04
- Next message: Ivan Arjentinski: "Re: Does index exist"
- Previous message: Erland Sommarskog: "Re: UTF-8 Conversion in SQL"
- In reply to: Paul fpvt2: "Re: Processing queries simultaneously"
- Next in thread: Paul fpvt2: "Re: Processing queries simultaneously"
- Reply: Paul fpvt2: "Re: Processing queries simultaneously"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Dec 2004 18:45:33 -0500
Are they really going to use 500K points for the chart<g>. Sounds like you
can aggregate it some first. Any way SQL Server will use multiple CPU's on
it's own if the query and conditions are appropriate. That basically means
you need to have a query and schema that will benefit from parallel
processing first. Then at run time it evaluates the amount of free memory,
CPU usage etc to see if it can run in parallel properly. If so it will, if
not it will run with less than the max # of procs or simply one. It sounded
from your first post that you didn't want it to use more than one proc since
it was holding up other users. If that is the case you can do one of two
things to limit the # of CPU's used by any one user at a time. Check out
MAXDOP in booksonline for more details. If the query is not using a lot of
CPU you probably have a bottle neck in one of two paces or both. One is the
I/O. If the disks can not produce the data fast enough the CPU's may have
to wait and thus they look less busy. The same is true if the client can
not process the rows sent fast enough. With that many rows I suspect it is
a combination of both. What is the client using to receive the result set?
Hopefully it is not fetching them one row at a time.
-- Andrew J. Kelly SQL MVP "Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message news:0B734D9A-0FF5-4348-B500-CD86BC49952B@microsoft.com... > Thank you for your reply. > I agree with you that I wish I do not have to return that much data. The > data is passed back to another application so that they can draw a chart > based on the data I send them. The query does not use a lot of CPU. > > The machine is a multiprocessor PC. Is there any setting that I need to > set > to make SQL server run on multiple CPU's ? > > Thanks. > > "Andrew J. Kelly" wrote: > >> Paul, >> >> You have several places where you can essentially serialize requests with >> such large result sets. The two biggest ones are the CPU and the I/O. If >> you only have a single CPU you are pretty much at the mercy of any poorly >> written queries that use lots of processor resources. The same pretty >> much >> holds true if your query requires disk access and there isn't enough >> bandwidth to get it all fast enough. But the real issue is why are you >> returning 500K rows? What is the user going to do with that many rows? >> It >> is imposable for a user to comprehend or effectively utilize more than a >> few >> thousand rows at tops. If you have further processing to do on those >> rows >> you may want to think about doing it in the SQL Engine and return just >> the >> results. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Paul fpvt2" <Paulfpvt2@discussions.microsoft.com> wrote in message >> news:4C63BB28-0146-4D0F-A55E-4A25719E18D0@microsoft.com... >> > We have VB program who queries data (using stored procedure) from a SQL >> > Server 2000 db. The database can grow as much as 10 million records. >> > When >> > the >> > user queries data, the data returned to the user varies between 10,000 >> > records to 500,000 records. The data is returned to the user through a >> > socket. >> > >> > When multiple users request data, there is a delay. For example, the >> > 1st >> > user requests data that returns 500,000 records, and if the 2nd user >> > requests data that returns 10,000 records, the 2nd user request is not >> > being >> > processed until the 1st user requests is completed. >> > >> > Is it possible for the 2nd user request to be executed while the 1st >> > user's >> > request is being processed, so that the 2nd user does not have to wait >> > for >> > the 1st user's request to be completed ? >> > Somebody suggested that it would require more than one CPU and SQL >> > server >> > can be run on servers with multiple CPU's (albeit he believes the >> > license >> > costs >> > more). Is this the best way to do it, and how can I do it ? >> > >> > Thank you very much. >> > >> > >> >> >>
- Next message: Ivan Arjentinski: "Re: Does index exist"
- Previous message: Erland Sommarskog: "Re: UTF-8 Conversion in SQL"
- In reply to: Paul fpvt2: "Re: Processing queries simultaneously"
- Next in thread: Paul fpvt2: "Re: Processing queries simultaneously"
- Reply: Paul fpvt2: "Re: Processing queries simultaneously"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
