Re: [Nolug] OT - MS SQL 7.0 Guru Needed

From: Friedrich Gurtler <fgurtler_at_tulane.edu>
Date: Wed, 01 Jun 2005 01:54:50 -0500
Message-ID: <429D5BBA.50201@tulane.edu>

A couple of suggestions.

First off, I would eliminate any concerns about the DB being abused by
the ASP site; I dont know how good your developers are, so its worth
checking.

    1) Make sure your connections from the asp pages are being properly
pooled. http://support.microsoft.com/default.aspx?scid=kb;[LN];191572
    2) Dont store recordsets in session variables; affects the above +
introduces threading issues.
    3) Check and make sure the ado calls are sane; avoiding looping over
recordsets in favor of using getRows for large sets, etc.
    4) Use parameterized stored procedures so the SQL server can cache
the execution plans.
    5) Use the appropriate cursor types and such.

Seriously though, if your developers are doing stuff like:

SQL = "Select * from t_table"
Rs.Open SQL, ConnectionString
While Not Rs.EOF
   Response.write Rs("field_name")
Wend

You wont get much out of your database. Its all fine for prototyping,
but if you have lots of users hitting it, its gonna start gagging.

Once you are sure your website isnt asking anything inordinate of the db:

    1) Run all your SPs through the "Show Execution Plan" in Query
Analyzer to check if you are doing any crazy joins / full table scans, etc.
       1a - create the appropriate indexes where you see full table scans

But seriously, you have to be doing something pretty crazy to max out a
SQL database running on any sort of decent hardware. I am willing to
bet the problem lies in the ASP pages abusing your poor DB. Just some
thoughts. I havent ever had to do really serious performance tuning on
MSSQL, so perhaps someone on the list will offer some more constructive
ideas.

Fritz Gurtler

Helmut Ermlich wrote:

> Hey Guys,
> I know this is a little (well maybe a lot...) off topic for a Linux
> list but I need a some help. Since most of you probably wear a bunch
> of IT hats, I was hoping that one of you may be or know of someone who
> is a real MS SQL Server 7.0 Guru. This is a little over my head but
> might be obvious to someone that is well versed in MS SQL Server 7.0
> and works with it every day.
>
> I run a fishing and hunting website called RodnReel.com. It is totally
> dynamic and uses MS SQL 7.0 as the backend DB. For some reason the DB
> is periodically choking and I am getting timeouts on the webpages that
> make queries to the DB. This has been happening over the past 4 weeks
> or so and is happening more and more frequently. I talked to Joey
> earlier today and he even came by to take a quick look and try a
> couple of things. We couldn't pinpoint where the bottleneck is and I
> don't know enough about MS SQL to know what to check. I've run a few
> query duration traces on the system but every time it is run, I get a
> different group of queries that come up taking extremely long times to
> complete. Not sure which query is dragging the system down and making
> the rest slow. CPU utilization is running close to 100% on the SQL box
> and the Webserver (IIS5 on a separate box) is less than 10% when this
> happens. Unfortunately, I don't know what buttons to push or knobs to
> turn to get an accurate picture of what is causing the bottleneck. (I
> know, I know... switch to mySQL and Apache, but short of doing
> that) It may be that we are just getting hammered on the website and I
> just need to throw faster hardware at it. ???
>
> Anyway, if any of you know of someone that really knows SQL and who
> might be willing to take a look at it (and maybe even get paid!
> ), please call me on my cell phone 504-858-3892. Any help is appreciated.
>
> Thanks,
> Helmut

___________________
Nolug mailing list
nolug@nolug.org
Received on 06/01/05

This archive was generated by hypermail 2.2.0 : 12/19/08 EST