Logo
HOW TO
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
 
 
Windows Server

SQL Server 2008 R2 : A Performance Monitoring Approach (part 1)

7/24/2013 9:38:04 AM

If you take a closer look at the performance monitoring areas depicted in Figure 1, you can see that SQL Server spans them all. SQL Server must process requests submitted to it via the network, service those requests with one or more processors, and rely on accessing a request’s data from both memory/cache and the disk system. If you maximize utilization on these resources from the point of view of SQL Server and the operating system, you end up with a well-tuned database server. However, an optimally tuned system doesn’t guarantee good performance. It’s still important to have a good database design and to implement optimal index strategies.

One area of interest is the amount of network traffic handled by SQL Server and the size of these network requests. Another area of interest is the capability of the available processors to service the load presented to them by SQL Server without exceeding the CPU capacity of the system. This section also looks at SQL Server memory utilization of the available system memory and how effectively SQL Server is utilizing the disk system.

In general, you want to start from the bottom, with the network, and work your way up into the SQL Server–specific elements. This allows you to quickly isolate certain issues that are paramount in performance tuning. In each of these areas, this section provides a list of minimum detail performance handles or counters that can be examined. This approach can be summarized into the following steps:

1.
Understand and monitor network request characteristics as they relate to SQL Server and the machine on which SQL Server has been installed. This means a complete profile of what is coming into and being sent back out over the network from SQL Server.

2.
Understand processor utilization. Processing power might be the biggest issue affecting performance. You need to get a handle on this aspect of performance early.

3.
Understand and monitor memory and cache utilization. This is the next step, monitoring the overall memory usage from the operating system level point of view and monitoring the memory that SQL Server is using for data buffers, plan cache, and so on.

4.
Understand and monitor disk system utilization. You are often rewarded for implementing a simple (and less expensive) disk configuration or data storage approach. However, a simple configuration may not provide the best performance for your system. And you often don’t know you have a problem unless you look for it. Techniques that are often used to alleviate disk performance issues include disk striping, isolation of logs from data, and so on.

You need to repeat steps 1 through 4 on a regular basis. Your continued success (and, it is hoped, salary increases) will reflect your diligence here. For each step, certain tools and facilities are available for you to use that gather the information needed to identify and monitor performance issues. Let’s look now at how to use these tools to monitor and analyze the network, processor, memory, and disk utilization and performance.

1. Monitoring the Network Interface

One area of possible congestion is the network card or network interface; it does not matter how fast the server’s work is if it has to queue up to go out through a small pipe. Remember that any activity on the server machine might be consuming some of the bandwidth of the network interface card. With the increasing implementations of gigabit networks and the increases in network bandwidth over wide area networks (WANs), network throughput is not as much of a bottleneck as it used to be. That’s not to say network performance issues do not arise. For example, a bad or misconfigured router can cause all sorts of network performance issues, especially if your users and your SQL Server are on different sides of the router. If you are not detecting query performance issues in SQL Server but users are still complaining about slow query performance, the network is a likely culprit.

You can monitor network activity via Performance Monitor. Table 1 shows the typical network performance object and counters you can use to monitor network interface activity.

Table 1. Network Interface Performance Objects and Counters
Performance Monitor ObjectDescription
Network Interface: Bytes ReceivedThe rate at which bytes are received on the interface.
Network Interface: Bytes SentThe rate at which bytes are sent on the interface.
Network Interface: Bytes TotalThe rate at which all bytes are sent and received on the interface.
Network Interface: Current BandwidthThe bits per second (bps) of the interface card.
Network Interface: Output Queue LengthThe length of the output packet queue (in packets). If this is longer than two, delays are occurring, and a bottleneck exists.
Network Interface: Packets ReceivedThe rate at which packets are received on the network interface.
Network Interface: Packets SentThe rate at which packets are sent on the network interface.
Network Interface: PacketsThe rate at which packets are sent and received on the network interface.
Server: Bytes ReceivedThe number of bytes the server has received from the network. This is the big-picture indicator of how busy the server is.
Server: Bytes TransmittedThe number of bytes the server has sent/transmitted to the network. Again, this is a good overall picture of how busy the server is.

In general, if the SQL Server packet sends or receives are grossly lower than the overall server’s packet sends and receives, other activity on the server is occurring that is potentially bogging down this server or not allowing SQL Server to be used optimally. The rule of thumb here is to isolate all other functionality to other servers if you can and let SQL Server be the main application on a machine.

The sp_monitor system stored procedure, as well as several SQL Server system variables, can be used to see how many requests are queuing up, waiting to make use of the network interface. The following SELECT statement retrieves a current picture of what is being handled by SQL Server from a network packet’s point of view:

SELECT  @@connections as Connections,
        @@pack_received as Packets_Received,
        @@pack_sent as Packets_Sent,
        getdate() as 'As of datetime'
go
Connections Packets_Received Packets_Sent As of datetime
----------- ---------------- ------------ -----------------------
39407       395569           487258       2010-05-16 22:34:19.650

The sp_monitor system stored procedure provides packets sent and received as a running total and since the last time it was run (difference in seconds). Here’s an example of what would result (the network- and packets-related results):

EXEC sp_monitor
GO
last_run                current_run             seconds
----------------------- ----------------------- -----------
2010-04-02 17:34:58.817 2010-05-16 22:35:08.940 3819610

cpu_busy     io_busy    idle
------------ ---------- ------------------
318(318)-0%  62(62)-0%  182952(182928)-4%

packets_received packets_sent    packet_errors
---------------- --------------- --------------
395781(395753)   487483(487455)  1(1)

total_read   total_write   total_errors connections
------------ ------------- ------------ -------------
8084(8084)   69921(69921)  0(0)         39432(39418)

The values within the parentheses are the amounts since the last time sp_monitor was run, and the seconds column shows how long that period was. You can actually see the rate at which traffic is coming into and out of SQL Server.

Other -----------------
- Microsoft Systems Management Server 2003 : The Four-Phase Patch Management Process (part 2) - The Evaluate & Plan Phase, The Deploy Phase
- Microsoft Systems Management Server 2003 : The Four-Phase Patch Management Process (part 1) - The Assess Phase, The Identify Phase
- Microsoft Systems Management Server 2003 : Patch Management - Preparing for Patch Management
- Microsoft Systems Management Server 2003 : Patch Management - The Need for Effective Patch Management, Introduction to the Patch Management Process
- Windows Server 2012 : Configuring post-installation settings
- Windows Server 2012 : Enabling and disabling the graphical interface in Hyper-V
- Windows Server 2012 : Managing a Server Core installation using sconfig
- SQL Server 2012 : Running SQL Server in A Virtual Environment - EXTENDED FEATURES OF VIRTUALIZATION
- SQL Server 2012 : Running SQL Server in A Virtual Environment - VIRTUALIZATION CONCEPTS
- SQL Server 2012 : Running SQL Server in A Virtual Environment - COMMON VIRTUALIZATION PRODUCTS
 
 
REVIEW
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
 
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
 
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
Top 10
- Microsoft Excel : How to Use the VLookUp Function
- Fix and Tweak Graphics and Video (part 3) : How to Fix : My Screen Is Sluggish - Adjust Hardware Acceleration
- Fix and Tweak Graphics and Video (part 2) : How to Fix : Text on My Screen Is Too Small
- Fix and Tweak Graphics and Video (part 1) : How to Fix : Adjust the Resolution
- Windows Phone 8 Apps : Camera (part 4) - Adjusting Video Settings, Using the Video Light
- Windows Phone 8 Apps : Camera (part 3) - Using the Front Camera, Activating Video Mode
- Windows Phone 8 Apps : Camera (part 2) - Controlling the Camera’s Flash, Changing the Camera’s Behavior with Lenses
- Windows Phone 8 Apps : Camera (part 1) - Adjusting Photo Settings
- MDT's Client Wizard : Package Properties
- MDT's Client Wizard : Driver Properties
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro