D
dbeavon
Guest
Can someone tell me their experience with scaling up SQL92? We need the SQL92 engine to support large numbers of concurrent connections. As an example, lets say that the remote ODBC clients hold on to their connections for long periods of time but use them infrequently. (eg. because the connection pools grow quickly, but don't get trimmed by the connection-pool-timeout until five minutes have elapsed). Currently that type of grown in remote clients can cause problems. The problems seem to be somewhat self-inflicted because the SQL92 engine has limited capacity for remote client connections (as determined by things like -n and -Mpb/-Ma on the secondary broker). We are tempted to configure the number of remote SQL92 connections to grow indefinitely (1000's) via -Mpb and -Ma. This is based on the fact that the remote client connections (part of an application's connection pool) are normally inactive and, when they do make queries they are resolved from memory buffers very quickly. There is low CPU and low disk overhead. My understanding is that SQL92 servers are able to scale up easily . I think they are designed to use true OS-based multi-threading (instead of some sort of "time-slicing" mechnanism). IE. if a single _sqlsrv2 process is working on more than one request at a time, then the operating system will allow the work to be done in parallel on separate cores. Is that correct? So what would be the impact of increasing -Ma very high number (eg. 100 or 200) and then increasing -Mpb at our discretion (based on the number of separate processes we can easily fit in memory, eg. 40 or 50)? When will we run out of resources? Which resources will we run out of first? I'm guessing the bottleneck is going to be TCP ports or system memory (which we have in abundance). Any personal experiences in this area would be greatly appreciated. The limits we have on -Mpb (10) and -Ma (50) seem to cause a lot of "self-inflicted" pain, and cause us to frequently run into the capacity of 500. It seems to me that our SQL92 servers can handle a much, much higher number of remote connections - especially if most of them remain inactive much of the time (until the five minute CPTimeout expires).
Continue reading...
Continue reading...