|
SQLSERVER User Connections The General StatisticsUser Connections of the SQL instance in the performance counter corresponds to it You can also refer to TCPv4Connections Established in the performance counter
On a machine with only the SQLSERVER service,TCPV4 Connections Established && Mssql UserConnectionsThese two parameters are basically synchronized and are referred to as the number of connections in the following test content
This number of connections is limited, similar to the maximum number of concurrency limits. When this limit is reached, it will cause queries to become unresponsive even if there are free CPU, IO, and MEM resources (some, not all queries are affected)
The maximum value of TCPV4 Connections Established && Mssql UserConnections depends on the following factors
a. The complexity of the execution statement is related, the more complex the statement, the smaller the maximum number of connections (this effect is very important)
b. It is related to the thread concurrency of the request,
10 processes, each process opens 5,000 threads to request,SELECT getdate()The statement is already abandoned when it reaches about 4000 (my understanding is that although many threads have not requested resources, the number of requests is large, which also has an impact).
10 processes, each process opens 1000 threads to request, SELECT getdate()Statements can reach more than 10,000.
c. It is related to the frequency of thread requests
If each thread runs a query and pauses for 0-10,000 milliseconds, it will take up fewer connections than if it is not suspended
d. In the test, it was also found that some unclosed connections would cause the counter to be inflated, which was not discussed
10 processes, each process opens 1000 request threads, randomly pauses (0 to 10 seconds) after each query on the server The test results are as follows:
Execution:SELECT * FROM [system]. [dbo]. [DBA_alert]statement (this query returns 200 lines, and the maximum number of connections is 700 and the problem starts to be issued)
When the number of connections reaches 700, some errors begin to be reported, and at 1200, a large number of errors begin to appear, and the number of connections around 1800 is stuck and no longer rises. Errors and sluggish connections are abundant
Execution:SELECT getdate()Sentence time(The maximum number of connections is 3500 and the problem starts )
When the number of connections reaches 3500, some of them begin to report errors, and the highest pressure reaches about 11000, and the number of connections is still slowly rising. Errors and sluggish connection blowouts
The conclusion is: under given pressure conditions: the simplest to performThe maximum number of connections can be up to 3500 when SELECT getdate(), and execute SELECT * FROM [system]. [dbo]. [DBA_alert] statement, the maximum number of connections can only be up to 700. Query timeout occurs when CPU, IO, and MEM all have a large number of idle resources.
In the production environment, the pressure of 10*1000 threads cannot be reached, but SQL will be more complex than the test environment.
Concurrent numberThe bottleneck is not caused by the bandwidth of my computer or server network card
To prove the above. I did the following tests, exec dbo.run2 in the case of large concurrency;
ALTER proc [dbo]. [run2]
as
set nocount on
select getdate()
declare @i int
set @i=0
while @i<1000 When this value is 1000, the maximum number of connections is about 1300, and when this value is 10, it is normal to reach 5000.
begin
INSERT INTO [pubs]. [dbo]. [tb_test] ([name]) values (newid())
set @i=@i+1
end
go
The number of change cycles will not affect the return result, that is, the pressure on the network card traffic is the same, but one will time out quickly and the other can always be checked, so the impact of the bandwidth of the network cards on both sides can be eliminated.
Error content when the maximum number of connections is stalled:
netstat-an result when maximum connection count is stuck (heavily established):
Test program running diagram when the maximum number of connections is stalled:
It is worth noting:The connection with errors or timeouts is not even, and will be concentrated in some processes, that is to say, some processes can always run normally, and the other part will report errors for a long time (it has nothing to do with the start order of the processes, my understanding is that some processes have resources, can continue to work normally, while other processes in the threads in the original resources are preempted and cannot apply for new resources, will keep repeatedly reporting errors) As shown in the figure above, the 2nd and 7th processes began to have a large number of timeouts, and other processes continued to work. On the server, queries may appear as unaffected or less affected by some machines, and some machines are significantly affected.
Conclusion: Although the upper limit of SQLSERVER User Connections is related to a series of conditions, it is still possible to estimate and predict the bottleneck, when this upper limit is reached, there will be a large number of queries slow and timeouts (although CPU, IO. MEM, traffic can also occur when there are idle resources). In fact, changing some TCP parameters will increase this upper limit, and I may write supplements later
|
Posted on 11/26/2014 3:11:08 PM
|
|
|
|
