Sql server client statistics update#
Sql server client statistics series#

However, it is not unusual that the application users start to complain that instead of getting results immediately or in a few seconds they have to wait tens of seconds or even minutes to get results. It can often be heard from developers that the application should use LAN or WAN connection with high throughput to get satisfactory performance. So after the development completes, the application has to be deployed in the customer environment and this is where the problem starts.Дepending on the type of the application, it can be deployed in the LAN (Local Area Network), WAN (Wide Area Network) or combined LAN-WAN environment When developing the application, developers usually work in a controlled environment with high-end machines and with the whole development environment is on the same machine as SQL Server, and thus close-to-zero delay in communication with a database.

It is now clear that application performance does not depend strictly and always on SQL Server, so letвЂ™s expand this a bit to get a better insight into scenarios when the high round-trip times could affect the application performance. The more data that has to be updated the longer time application must wait on the round-trips.

Sql server client statistics update#

Even when a single update SQL query is issued, the application must wait a minimum of one round-trip time for each row updated in the target table. The most obvious scenario where inefficiency relating to the SQL protocol is the most noticeable is update statements. The number of round-trips in such cases can increase exponentially causing an additional increase in the total round-trip time. WhatвЂ™s more, in a situation where a single query has to return a substantial amount of data, more than one round-trip could be experienced for that query, which depends on the network TCP protocol. Looking at the diagram above it is clear that even in an ideal scenario, where SQL Server query processing time is zero, the application must wait for at least a sum of SQL Server round-trip times for each query execution. In other words, an application that interacts with SQL Server is often forced to spend excessive time waiting for handling all requests/responses duos on the network.

Since applications tend to handle their data in a way that minimizes the data volume transferred in each interaction with the database, the result is usually that a significant number of requests/responses are needed for dealing with even modest data requests.

Sql server client statistics series#

It as a very talkative protocol where the client can create a series of requests to the database, each request must wait for the response on the previous one before it can be sent. SQL protocol is designed to rely on intensive communication between the client and SQL Server. However, to be able to understand why and how the round trip affects the SQL Server performance, as first, it is vital to understand the SQL protocol itself. The latency or so-called SQL Server round-trip time (RTT) is something that is often overlooked by DBAs and database developers, but excessive round-trip time can severely affect performance. Two factors affect network performance: latency and throughput.

As all communication between an application and SQL Server goes via some network (LAN or WAN), network performance could be a critical factor affecting overall performance.

For every query issued by the application, time is needed to reach the SQL Server and then the time needed for results to get back to the application.