Database Reference
In-Depth Information
In Windows Server 2012 R2, you can install the Network Monitor Driver from the local area connection properties
for the network adapter. The Network Monitor Driver is available in the network protocol list of network components
for the network adapter.
You can also look at the wait statistics in sys.dm_os_wait_stats for network-related waits. But, one that frequently
comes up is ASYNC_NETWORK_IO. While this can be an indication of network-related waits, it's much more
common to reflect waits caused by poor programming code that is not consuming a result set efficiently.
Network Bottleneck Resolutions
A few of the common network bottleneck resolutions are as follows:
•
Optimizing application workload
•
Adding network adapters
•
Moderating and avoiding interruptions
Let's consider these resolutions in more detail.
Optimizing Application Workload
To optimize network traffic between a database application and a database server, make the following design changes
in the application:
•
Instead of sending a long SQL string, create a stored procedure for the SQL query. Then, you
just need to send over the network the name of the stored procedure and its parameters.
•
Group multiple database requests into one stored procedure. Then, only one database request
is required across the network for the set of SQL queries implemented in the stored procedure.
•
Request a small data set. Do not request table columns that are not used in the application logic.
•
Move data-intensive business logic into the database as stored procedures or database triggers
to reduce network round-trips.
•
If data doesn't change frequently, try caching the information on the application instead of
frequently calling the database for information that is going to be exactly the same as the last call.
•
Minimize network calls, such as returning multiple result sets that are not consumed. A
common issue is caused by a result set returned by SQL Server that includes each statement's
row count. You can disable this by using
SET NOCOUNT ON
at the top of your query.
SQL Server Overall Performance
To analyze the overall performance of a SQL Server instance, besides examining hardware resource utilization,
you should also examine some general aspects of SQL Server itself. You can use the performance counters
presented in Table
4-3
.
