Increase logons for MSDE

Question

Increase logons for MSDE

Stormin Norm

SSC Rookie

Points: 32
More actions
March 3, 2006 at 8:57 am

#152802

We are running Outlook BCM. It installs SQL MSDE automatically. Is there a way to increase the amount of logons allowed??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

There is not a maximum number of logons. You might mean maximum number of connections. By default, that should be 32,767. Run SELECT @@MAX_CONNECTIONS to be sure. If it is not 0, you should be able to run sp_configure('user connections',0) to change it (you will need to restart SQL Server for it to take effect).

You also may be referring to the MSDE workload governor. This is really the only limit on MSDE other than what is standard in SQL Server standard edition. This is not a limitation on logons or user connections. But when MSDE detects there are more than 8 concurrent queries executing at a time, it will start throwing 'NOOP' instructions to slow down processing. All queries will still run, no one will be denied services; it will just take a little longer. This cannot be removed or bypassed in MSDE, you can only upgrade to SQL Server standard edition.

Hoe this helps

Mark

Stormin Norm SSC Rookie Points: 32 More actions · Answer 2

Thanks Mark!

GREAT info. That explains why someone at a Microsoft technet event said you can only run 5 concurrent.

In another week or two we should be up to five or eight concurrent users in BCM/MSDE. I'll be very interested to see how badly performance degrades

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

But understand that the governor is for concurrent database QUERIES, not concurrent users. Because most applications, web or desktop, spend most of their time working with data, not retrieving the data, the number of active concurrent users can be much greater.

Many years ago (in '97), I had written a hybrid (desktop and intranet) application that used ODBC connection pooling for a investment company (app was for securities trading). We monitored the server (application and database) on an extremely heavy day. The servers were dual Pentium Pros (PII/200mhz), certainly not speedy by today's standards. There were about 1500 brokers using the system from 9am-3pm, submitting 240K trades, about 2 million database queries. Monitoring the performance for the database server during that period, the maximum number of connections to the database was 8. 90% of the time it was less than 4 connections.

In normal applications, the amount of time actually spent doing database queries, is usually very, very small. Unless of course, your developers wrote terribly inefficient queries. 5 concurrent database queries can actually service dozens, if not hundreds of actual users. Although I am not familiar with the workings of BCM, I would be surprised, that when you go to 5 or 8 users, there will be a noticable difference.

Mark