MS SQL Server - VB6 Frontend, User connections and SQL Server Processes

Author

VB6 Frontend, User connections and SQL Server Processes

Marco Pais

2005-03-31, 7:01 am

Hi there,

I have a VB6 application working over a SQL Server 2000 database. since a
long time ago, I am experiencing serious performance problems, that I still
can't resolve. Normaly, the performance problems are related with database
design, query design, application design an hardware. The last one is
excluded, since we have a HP ML350 (double XEON) with 2.5GB RAM as server.
So the problem must result of defective design. Therefore, I have some
questions that I hope to be answered.

DatabaseCode Design:

1. As the VB6 application starts, a connection is created and this same
connection is used in all application processes. Is recomended using always
a new connection? Shall I close the connections and record sets, and recreat
it every time I need to access the database?

2. There are always10-15 workstations using the applications. All of
them access the database with 'sa' user. Is recomended using diferent users
in each workstation? For example, is better using the correspondent Active
Directory user, and then using Windows Authentication Mode?

I have this doubts because I executed some queries and I have found that,
even when there are only 3-4 users working, there are allways like 60, 80 or
even 100 open database connections. Processes of 'sa' user 'Awaiting
command' are even more! This is not normal, I supose.

Can anyone help me out? This is getting out of my nerve...

Thanks!

Regards,

Marco Pais

Mike Epprecht (SQL MVP)

2005-03-31, 7:01 am

Hi

It is very bad practive to have applications log in as 'sa'. In effect, the
users have permission to delete all the data, drop all the tables and destroy
your server.

Depending on how badly the application is written, it might open a DB
conenction for each form, so having multiple connections per workstation is
possible. When a connection is not moving data around, it has a status of
'Awaiting Command'.

Have a look a security in BOL.

Regards
Mike

"Marco Pais" wrote:

> Hi there,
>
> I have a VB6 application working over a SQL Server 2000 database. since a
> long time ago, I am experiencing serious performance problems, that I still
> can't resolve. Normaly, the performance problems are related with database
> design, query design, application design an hardware. The last one is
> excluded, since we have a HP ML350 (double XEON) with 2.5GB RAM as server.
> So the problem must result of defective design. Therefore, I have some
> questions that I hope to be answered.
>
> DatabaseCode Design:
>
> 1. As the VB6 application starts, a connection is created and this same
> connection is used in all application processes. Is recomended using always
> a new connection? Shall I close the connections and record sets, and recreat
> it every time I need to access the database?
>
> 2. There are always10-15 workstations using the applications. All of
> them access the database with 'sa' user. Is recomended using diferent users
> in each workstation? For example, is better using the correspondent Active
> Directory user, and then using Windows Authentication Mode?
>
> I have this doubts because I executed some queries and I have found that,
> even when there are only 3-4 users working, there are allways like 60, 80 or
> even 100 open database connections. Processes of 'sa' user 'Awaiting
> command' are even more! This is not normal, I supose.
>
> Can anyone help me out? This is getting out of my nerve...
>
> Thanks!
>
> Regards,
>
> Marco Pais
>
>
>

Marco Pais

2005-03-31, 7:01 am

I understand the security risks when using the 'sa' login. But can this
issue generate performance problems? Can the application be faster if using
Windows Authentication?

Thanks,

Marco

"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:B26B403F-1803-42DA-8377- 5778F05DDB67@microso
ft.com...[color=darkred]
> Hi
>
> It is very bad practive to have applications log in as 'sa'. In effect,
> the
> users have permission to delete all the data, drop all the tables and
> destroy
> your server.
>
> Depending on how badly the application is written, it might open a DB
> conenction for each form, so having multiple connections per workstation
> is
> possible. When a connection is not moving data around, it has a status of
> 'Awaiting Command'.
>
> Have a look a security in BOL.
>
> Regards
> Mike
>
> "Marco Pais" wrote:
>

Norman Yuan

2005-03-31, 8:01 pm

The VB app design indeed sounds bad. Using "sa" for everyone is not
acceptable in any sense, but it is not the reason of slowness. For a
database server, such as SQL Server, for each front end application instance
to open a connection and keep it open til the app is closed is also a bad
design, especially if the number of fron end app instatnce is big. In your
case, although 10 to 15 workstations will not be considered big number,
still, keeping many connections open all the time is bad design. And are
your sure the bad app not openning more connections for different forms and
not closing them properly?

Good design is the open connection to the SQL Server only at the moment when
the app need to query/update data, the close it immediately when the data
access processing is done. SQL Server (and almost all database server) uses
connection pool, meaning there alway certain number of connections are kept
open by SQL Server to reduce overhead for opening/closing connections. When
your app opens connection, SQL Server hands out an open connection from the
pool if there is one available; when your app closes connection, that
connection may not be actually closed, it is simply handed back to the pool.
The thumb of rule for fron end app is to delay opening connection until
absolutely necessary, and close the connection ASAP.

The other reason for the slowness could be the app uses dynamic SQL
statements in the app rather than stored proceddures in the SQL Server
database, which is also considered as not best practice when using SQL
Server.

"Marco Pais" < mpaisIGNORE@foruminf
ormatica.pt> wrote in message
news:OSqRlNdNFHA.3788@tk2msftngp13.phx.gbl...
> Hi there,
>
> I have a VB6 application working over a SQL Server 2000 database. since a
> long time ago, I am experiencing serious performance problems, that I
still

> can't resolve. Normaly, the performance problems are related with database
> design, query design, application design an hardware. The last one is
> excluded, since we have a HP ML350 (double XEON) with 2.5GB RAM as server.
> So the problem must result of defective design. Therefore, I have some
> questions that I hope to be answered.
>
> DatabaseCode Design:
>
> 1. As the VB6 application starts, a connection is created and this
same
> connection is used in all application processes. Is recomended using
always
> a new connection? Shall I close the connections and record sets, and
recreat
> it every time I need to access the database?
>
> 2. There are always10-15 workstations using the applications. All of
> them access the database with 'sa' user. Is recomended using diferent
users

> in each workstation? For example, is better using the correspondent Active
> Directory user, and then using Windows Authentication Mode?
>
> I have this doubts because I executed some queries and I have found that,
> even when there are only 3-4 users working, there are allways like 60, 80
or
> even 100 open database connections. Processes of 'sa' user 'Awaiting
> command' are even more! This is not normal, I supose.
>
> Can anyone help me out? This is getting out of my nerve...
>
> Thanks!
>
> Regards,
>
> Marco Pais
>
>

Sponsored Links