-
Titel
Changing network packet size from SQL Server agent connections -
Beschreibung
When verifying session information in the SQL Server instance it can be seen that the network packet size for Foglight connections is 32576 bytes. This can have a performance impact in the environment.
select DISTINCT sess.login_time,net_packet_size, sess.program_name from sys.dm_exec_connections as con join sys.dm_exec_sessions as sess on sess.session_id = con.session_id where sess.program_name LIKE '%Foglight%' ORDER by sess.login_time desc -
Ursache
The SQL Server agent uses a default Packet Size of -1 in the DataDirect connection driver, which uses the maximum packet size (32576 bytes) that is set by the database server.
When the packetSize set to a default of 0, the jdbc connection packet size would be 4096 bytes, this maps to packetSize=8
The maximum effective packetSize is 63 with 32256 bytes.
When the packetSize set to 64, the actual packet in bytes is 32576.
The packetsize of 16 is 8192 bytes.
-
Lösung
The following groovy script can be used to set the packetsize using the monPacketsize variable. In this example script the monPacketsize ASP value has been set to 16.
Replace the AGENTNAME with the name of the specific SQL Server agent.
def agentName = "AGENTNAME"; // find the agent def agents = server.get("AgentService").findByName(agentName); if (agents.size() == 0 ) { return String.format("Failed to find agent: %s", agentName); } if (agents.size() > 1 ) { return String.format("More than 1 agents were found to: %s", agentName); } def agent = agents.get(0); // update agent ASP def configService = server.get("ConfigService"); def primaryAsp = configService.getAgentInstancePrimaryAsp(agent.getAgentNamespace(), agent.getTypeId(), agent.getId()); primaryAsp.setValueByString("monPacketSize","16"); configService.saveConfig(primaryAsp); return String.format("Agent %s ASP successfully updated", agentName);