SQL Concurrent Limit

system · 2007-05-29T15:12:07.000Z

Hello,
I have installed Avast and the ADNM(with MSDE) to our SBS standard edition server.
I have so far only deployed Avast to one XP client station.
It all seems to be going well, but…

Examining the file ERRORLOG (in the LOG folder under MSQL$AVAST) I see occurences of
“This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by (n) queries and performance may be adversely affected.”

(n) is usually 1 or 2

Have I done something wrong?
Is this going to get worse as I add workstations? Can I do something about it?!

Any help will be much appreciated.
Regards
ADF
(Previous experience of SQL-Nil)

system · 2007-05-30T09:24:29.000Z

Hello,

I’ve the same problem and server is very slow because disk access for the database use 100% disk time.
some time i can’t log on Console, (time out to logon adnm).

I reinstall a new server with a new database on a second physical disk and i’ve the same problem. (100% disk time just on the second disk).

Adnm 4.7.657 with about 400 clients, SQL 2000 sp4 personnal edition database.

Regards
JDI

system · 2007-06-07T10:02:49.000Z

Its quiet here…

Can nobody tell me more about these log entries… I’m trying to decide whether to go with this solution but am unsettled by these limit warnings.
This is an extract from the log:

2007-06-03 22:00:46.89 spid62 Database model: IO is frozen for snapshot
2007-06-03 22:00:46.89 spid64 Database msdb: IO is frozen for snapshot
2007-06-03 22:00:46.89 spid65 Database avast: IO is frozen for snapshot
2007-06-03 22:00:46.89 spid63 Database master: IO is frozen for snapshot
2007-06-03 22:00:51.26 spid63 Database master: IO is thawed
2007-06-03 22:00:51.26 spid64 Database msdb: IO is thawed
2007-06-03 22:00:51.26 spid62 Database model: IO is thawed
2007-06-03 22:00:51.26 spid65 Database avast: IO is thawed
2007-06-03 22:00:52.10 backup Database backed up: Database: model, creation date(time): 2005/05/03(23:26:11), pages dumped: 1, first LSN: 4:322:1, last LSN: 4:324:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{12B0D3B7-6EE5-4CBC-A381-1D355EC3F9FA}2’}).
2007-06-03 22:00:52.32 backup Database backed up: Database: master, creation date(time): 2007/05/25(11:14:39), pages dumped: 1, first LSN: 84:234:1, last LSN: 84:236:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{12B0D3B7-6EE5-4CBC-A381-1D355EC3F9FA}1’}).
2007-06-03 22:00:52.33 backup Database backed up: Database: msdb, creation date(time): 2005/05/03(23:27:33), pages dumped: 1, first LSN: 14:100:1, last LSN: 14:103:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{12B0D3B7-6EE5-4CBC-A381-1D355EC3F9FA}3’}).
2007-06-03 22:00:52.41 backup Database backed up: Database: avast, creation date(time): 2007/05/25(11:14:52), pages dumped: 1, first LSN: 9715:336:1, last LSN: 9715:338:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{12B0D3B7-6EE5-4CBC-A381-1D355EC3F9FA}4’}).
2007-06-03 22:01:51.31 spid12 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 2 queries and performance may be adversely affected.
2007-06-04 11:22:17.92 backup Database backed up: Database: avast, creation date(time): 2007/05/25(11:14:52), pages dumped: 615, first LSN: 10281:293:1, last LSN: 10281:297:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘avast_backup’}).
2007-06-04 22:00:40.21 spid11 This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 5 queries and performance may be adversely affected.

system · 2007-06-11T08:44:02.000Z

Should it be of any interest or help to anyone else, I got these log entries explained by avast tech support as follows…

refering to this part of the log:

2007-06-04 22:00:40.21 spid11 This SQL Server has been optimized for
8 concurrent queries. This limit has been exceeded by 5 queries and performance may be adversely affected.

I’d say you can safely ignore these. The thing is, MSDE (as opposed to the full SQL Server engine) is intentionally crippled so that it limits the number of concurrent connections allowed to the database. In other words, if the number of connections to the SQL engine exceeds a fixed threashold (which is, I believe, 5 [even though the log entry suggests it’s actually 8]), other attempts to connect to the database wait (they don’t fail, though). So, this slightly affects the performance of the DB engine but doesn’t really cause it to malfunction in any way.

This is why we generally don’t recommend use of MSDE if ADNM is servicing more than a couple of hundreds of clients. Our load tests showed that this is the number of clients with which this limitation of MSDE starts to cause noticable problems (i.e. the client connection timeouts start occuring).

Announcements