|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Re: SQL Performance Issues. Opinions Welcomed!!!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: SQL Performance Issues. Opinions Welcomed!!!
|
|
| Larry Kippenhan 2002-08-13, 12:23 pm |
| Another thing to take a look at is the application.
Try executing the query in query analyzer and putting the results into a temp
table and see how long that takes (this will tell you about how long the server
is taking to return the recordset), or just execute the query as is in query
analyzer and see how fast that works (this will tell you how long it is taking
to send the raw data to your workstation as well as the execution time for the
query).
It is possible that the application is the bottleneck.
If it's not a simple select, have you tuned the query?
Just a couple of thoughts.
Larry
"Onslow" <nospam.gambhir@rogers.com> wrote:
>I'm running SQL Server 7 under Small Business Server. The database is
>taking 30 seconds to bring up 24,000 records into a browse screen in the VB
>client app. The network is 100 base T on both ends with a switch in the
>middle. The server is an HP Netserver e60, PIII 550 with a Gig of ECC RAM.
>I loaded up the server with RAM in the hopes of bettering the performance.
>It helped a little--shaved about 5 seconds off the time.
>
>The server is running the usual SBS stuff, Exchange, FaxServer. But there's
>only 4 clients right now, so it's a pretty light load.
>
>I'm not sure where the bottleneck is. Here are my options, as I see them:
>
>1) Buy a new server that has faster disks and processor and the same amount
>of RAM, but still run SBS.
>
>2) Move the database onto a seperate server that only runs SQL Server--to
>reduce any overhead that SBS has. Will SBS allow another server in the same
>domain?
>
>3) Move the database to MYSQL in the hopes of getting better performance
>since MYSQL has less overhead than either SBS or SQL Server on it's own
>(doesn't it?). Were I to do this, I might also consider running it under
>Linux since that will make the OS overhead very small and hopefully provide
>killer performance. Problem is, I'd have to learn Linux.
>
>
>Any comments on these ideas would be welcomed (really!!!) and ideas other
>than these would be great.
>
>Thanks.
>
>Robin.
>
>
| |
| Matthew Bando 2002-08-14, 9:23 am |
| Definitely try running the query in the Query Analyzer. Also, run the SQL
Profiler while running the query from the VB app. By default, ADO dynamic,
server side cursors use a cachesize of one row (there is one SQL get next
row request for each row needed at the client) so that for 24,000 records to
be displyed in a grid in a VB app, the SQL Server executes the query, caches
the data in RAM on the server, and then sends one row to the client, waits
for the client to request the next row, receives the request for the next
row, sends one row, etc 24,000 times.
In the SQL Profiler this will show up as a long series of sp_cursorfetch
statements being executed. If this is the case, you can speed this up by
changing the VB app's recordset object to use a cachesize greater than one
(like 100 or 500).
If you see the cursor activity in SQL Profiler but don't have access to
modify the VB code, there's not alot to do.
I hope that this helps.
Matthew Bando
BandoM@CSCTechnologies.com
"Larry Kippenhan" <LWKNOSPAM@att.net> wrote in message
news:#w7NXFvQCHA.2660@tkmsftngp12...
> Another thing to take a look at is the application.
>
> Try executing the query in query analyzer and putting the results into a
temp
> table and see how long that takes (this will tell you about how long the
server
> is taking to return the recordset), or just execute the query as is in
query
> analyzer and see how fast that works (this will tell you how long it is
taking
> to send the raw data to your workstation as well as the execution time for
the
> query).
>
> It is possible that the application is the bottleneck.
>
> If it's not a simple select, have you tuned the query?
>
> Just a couple of thoughts.
>
> Larry
>
> "Onslow" <nospam.gambhir@rogers.com> wrote:
> >I'm running SQL Server 7 under Small Business Server. The database is
> >taking 30 seconds to bring up 24,000 records into a browse screen in the
VB
> >client app. The network is 100 base T on both ends with a switch in the
> >middle. The server is an HP Netserver e60, PIII 550 with a Gig of ECC
RAM.
> >I loaded up the server with RAM in the hopes of bettering the
performance.
> >It helped a little--shaved about 5 seconds off the time.
> >
> >The server is running the usual SBS stuff, Exchange, FaxServer. But
there's
> >only 4 clients right now, so it's a pretty light load.
> >
> >I'm not sure where the bottleneck is. Here are my options, as I see
them:
> >
> >1) Buy a new server that has faster disks and processor and the same
amount
> >of RAM, but still run SBS.
> >
> >2) Move the database onto a seperate server that only runs SQL Server--to
> >reduce any overhead that SBS has. Will SBS allow another server in the
same
> >domain?
> >
> >3) Move the database to MYSQL in the hopes of getting better performance
> >since MYSQL has less overhead than either SBS or SQL Server on it's own
> >(doesn't it?). Were I to do this, I might also consider running it under
> >Linux since that will make the OS overhead very small and hopefully
provide
> >killer performance. Problem is, I'd have to learn Linux.
> >
> >
> >Any comments on these ideas would be welcomed (really!!!) and ideas other
> >than these would be great.
> >
> >Thanks.
> >
> >Robin.
> >
> >
>
|
|
|
|
|