vb.net/sql server speed issue

[Chandler Dickinson]

I have a vb.net application running with sql server... All calls to the
database are hit and drop.... i keep no connections open.... there are two
timers in the application that hit the database to check for new/changed
information (small hits)... here is the problem...

this one has be whipped... please help if you can...

I have only about 80 to 100 users peak... on mondays and fridays my users
are less and the application runs fine but tuesday-thursday my app will run
fast as lightning unit about 10:00am when the user counts go from 50-60 to
80-100 and then BAM the entire application on all clients machines come to
a screeching halt... and will remain that way until a few (5-7) users exit
the application.... then the speed will go back to light speed... Also...
it appears that on the first day after putting the database on a new server
it screams regardless of the number of users... but on day 2 the problem
comes back... I have actually had this thing running in the past on a MSDE
sql server with the same results... DB, Connections, my App?????? What the
heck is going on help help help...
Chandler Dickinson

 

[DalePres]

There's not much information to go on, but I suggest that you look at
indexes. Run some traces using SQL Profiler and then run the index tuning
wizard in SQL Profiler.

Dale

 

[Joe Fallon]

MSDE is the same engine as SQL Server but it has 2 major limitations.
Looks like you are running into #2 below.

1. The max DB size is 2GB.

2. There is a "governor" in place that throttles back performance when more
than 5 concurrent processes are running.

MS recommends that you use a full version of SQL Server to develop your
database on.
You really need all of the tools available like QA and EM, etc.

The idea would be for you to install MSDE for free at the client site and
the restore your database to it (or build it from scratch using commands
that can be genearted from EM.)

MSDE is the SQL Server engine with a couple of limitations like 2 GB of data
and 5 concurrent *processes* (that is not the same thing as users.) Over 5
processes, and the throttle kicks in to slow things down. There is a command
in MSDE (DBCC CONCURRENCYVIOLATION) which shows how many times the throttle
has kicked in. You are supposed to run it weekly or monthly and see if it is
time to upgrade to a full version of SQL Server. (BTW, a well designed app
should run so fast that you might be able to support 50-100 simultaneous
users without bumping into the throttle.)

 

[Chandler Dickinson]

Well this is my concern and that of my client... it is assumed that the
application was not designed well... but i can't imagine anything i did
wrong... i have a external dll containing all my data calls using the
dataadapter hit the database build a dataset and drop the connection....
return the dataset to the calling proceedure and that's that... this does
not seem wrong does it... your help is appreciated...

Chandler

 

[Joe Fallon]

That is the right design.
But that doesn't mean you aren't running into the governor.
A lot depends on how the app is used.
Many apps, the user spends a lot of time reading screens.
If in your app they are "always" fetching data then the governor could kick
in.

Did you try running DBCC CONCURRENCYVIOLATION to see the output?

 

[[MSFT]]

Hi Chandlder,

I suggest you may first moniter the connections statics on the SQL server.
add the following counters in Windows NT performance monitor tool under the
SQL Server:General Statistics performance object:

- Logins/Sec
- User Connections

When your application slow down, you may check these counters to see if the
connections/logins increase to peak and cause the problem.

Additionally, due to the connections pool in ADO.NET, aconnection to
database won't be dispose right way. It may saty in the pool for a while.
You may try to disable the connection pool if the problem is actually
caused by too much connection on the server. For more information on this,
you may refer to:

Connection Pooling for the .NET Framework Data Provider for SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconconnectionpoolingforsqlservernetdataprovider.asp

Hope this help,

Luke
Microsoft Online Support

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

 

[Chandler Dickinson]

Thanks for the reply.... my app actually hits the database grabs the data
and then drops the connection... i do not keep any connections open long...
but do however hit it often... i do notice that there are many sleeping
processes on the app... is this an indication that pooling should be
disabled... thanks for your help...

Chandler

 

[Chandler Dickinson]

Thanks for you help... not sure you are clear that I am running on SQL
Server Standard now not MSDE... is/can the governer still be an issue even
with this ver of SQl?

Thanks alot!

Chandler

 

[[MSFT]]

Hi Chandler,

In ADO.NET, the connection won't be disconnected right after we close it.
It will be saved in connection pool for a while and wait for next use.
Therefore, disable the connection pool is a possible solution.

I have another idea for the problem. Normally, a SQL server wil accept more
3000 connections. (In SQL Server Enterprise Manager, right click the server
and select Properties/Connections, if the Max connections has been set to 0
(unlimited) here, we can have more than 3000 connections) I suggest you may
try keep a live connection, not close the connection until your application
exit. Since you have 80~100 users, the connection number should be
acceptable.

Luke
Microsoft Online Support

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