SQL Server and IUSR account

[Michael Jensen]

I have recently been brought on to a project that is
integrating SQL Server 2000 as a new backend choice for
an .asp application (now supporting both Access 2000 and
SQL Server 2000). The primary developer OO'd the code to
death and manages ADODB objects (i.e. connections,
recordsets) through collections and is supposedly handling
the closing/de-allocation of said objects in a thourough
manner. The second issue that comes into play with our
problem is that there is no security (user authentication)
being used. As of now we are using the IUSR_MACHINE
account which is granted logon access to SQL Server and
access to the application database (the only saving grace
is that the application is run on private networks due to
data sensitivity reasons), which means that everyone who
uses the application is being "authenticated" as the
anonymous user. Everything works well with a small group
of users but when they have about 30+ people hitting
against the database IIS is brought to it's knees and
stops responding/serving out the .asp pages. The
developer in question is an Access guru and uses "SELECT
*" for just about every query, which I know from being a
SQL2K DBA, is bad mojo for SQL Server and does not take
advantage of indexes and other query enhancements SQL2K
provides. My question is, do all these combinations of
non best-practices spell disaster to our SQL Server
effort? Trying to convince the lead developer that these
things are going to hurt us is like pulling teeth. Are
there any thoughts on some performance tuning to IIS that
may alleviate some of the problems?

[William Tasso]

Michael Jensen wrote:

> Are
> there any thoughts on some performance tuning to IIS that
> may alleviate some of the problems?

You have got SQL Server on its own machine already?

What else is the web server doing?

--
William Tasso - [url]http://WilliamTasso.com[/url]

[Ken Schaefer]

SELECT * will use indexes in SQL Server 2000 if they are appropriate (check
you execution plan). The problem with SELECT * is that you could end up
returning lots of columns that you don't need, which wastes network
bandwidth, and SQL Server processing power.

That said, these are probably the big killers:
a) using expensive ADO cursors (like adOpenStatic)
b) using cursors in Stored Procedures (
c) concatenating large number of strings together in VBScript

That said, I think you need to use something like SQL Server Profiler to
work out if SQL Server is the bottleneck. If SQL Server is happily
responding to queries quite quickly, but the pages are being returned to the
users very slowly, then the problem is with the webserver (ie, something
wrong with the webserver -or- badly coded ASP pages)

Cheers
Ken

"Michael Jensen" <mjensen1@csc.com> wrote in message
news:027901c36da4$79dbadd0$a601280a@phx.gbl...
: I have recently been brought on to a project that is
: integrating SQL Server 2000 as a new backend choice for
: an .asp application (now supporting both Access 2000 and
: SQL Server 2000). The primary developer OO'd the code to
: death and manages ADODB objects (i.e. connections,
: recordsets) through collections and is supposedly handling
: the closing/de-allocation of said objects in a thourough
: manner. The second issue that comes into play with our
: problem is that there is no security (user authentication)
: being used. As of now we are using the IUSR_MACHINE
: account which is granted logon access to SQL Server and
: access to the application database (the only saving grace
: is that the application is run on private networks due to
: data sensitivity reasons), which means that everyone who
: uses the application is being "authenticated" as the
: anonymous user. Everything works well with a small group
: of users but when they have about 30+ people hitting
: against the database IIS is brought to it's knees and
: stops responding/serving out the .asp pages. The
: developer in question is an Access guru and uses "SELECT
: *" for just about every query, which I know from being a
: SQL2K DBA, is bad mojo for SQL Server and does not take
: advantage of indexes and other query enhancements SQL2K
: provides. My question is, do all these combinations of
: non best-practices spell disaster to our SQL Server
: effort? Trying to convince the lead developer that these
: things are going to hurt us is like pulling teeth. Are
: there any thoughts on some performance tuning to IIS that
: may alleviate some of the problems?

[MSFT]

It is quite a big topic to discuss about IIS+SQL performance, I would like
suggest following articles first:

ASP Tips to Improve Performance and Style
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasp/html/[/url]
asptips.asp


Maximizing the Performance of Your Active Server Pages
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasp/html/[/url]
maxperf.asp


Improving ASP Application Performance
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnserv/html[/url]
/server03272000.asp

You may find some helpful tips for youe application. Hope this help.

Luke
Microsoft Online Partner Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[MSFT]

For security issue, you may refer to:

Implementing a Secure Site with ASP
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/ht[/url]
ml/msdn_implement.asp

Luke
Microsoft Online Partner Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)