TechBubbles Microsoft Technology BLOG

Analyzing SQL Server Performance using Performance Monitor Counters

This post discusses about using Performance Monitor tool to create a base-line using different performance counters. It also discusses analyzing hardware resource bottlenecks and retrieving performance monitor data in sql server using views. Performance Monitor Tool collects the detailed information of system resources including memory, processor , disk and network.SQL Server 2008 provides extensions to Performance Tool to analyze various areas in SQL Server.

Performance Monitor tool provides performance counters to represent the specific aspects of a resource.

 

What is Performance Counter?

Performance Counter is a value of performance object.

example:

Performance object: Processor

Counter: % Processor Time

The sys.dm_os_performance_counters view displays the SQL Server counters within a query.
Following query returns the values for Logins per sec

   1: SELECT cntr_value 

   2: FROM sys.dm_os_performance_counters

   3: WHERE OBJECT_NAME = 'kalyan-pc'

   4: AND counter_name = 'Logins/sec'

There are several DynamicManagementViewes(DMV) to collect the information about SQL Server.

The DMV sys.dm_os_wait_stats shows the aggregated view of threads that are waiting for resources on SQL Server.

The following query is an example for this DMV

image

Common types of wait is I/O. If you see ASYNCH_IO_COMPLETION or IO_COMPLETION under wait_type column in above result then you may experiencing I/O contention. Read more about wait_types here

Identifying Hardware Resources Bottlenecks 

The most common bottleneck is usually I/O either from memory or from the disk. use the above mentioned DMV to identify these bottlenecks.

Bottleneck Resolution Decrease the arrival rate of requests to the resource.

example: adding appropriate indexes to limit the amount of data accessed or creating a partition on table.

Memory Bottleneck SQL Server uses memory pool to manage data and query execution plans. SQL Server manages this memory pool by growing or shrinking the size dynamically. You can configure this Dynamic Management memory in SSMS as shown below. right click the server and go to properties and select memory page

image

This Dynamic memory can be configures using two properties in above dialogue

Minimum server memory(in MB) it is the floor value for the memory, once it reaches to this value then it starts committing pages.

Maximum server memory(in MB)  it is the ceiling value to limit the maximum value of memory pool.

Microsoft recommendation is use 0 min property and max server memory will be maximum physical memory of the system. assume you are running single instance on server.

Performance Monitor Counters to analyze the Memory

Counter Name Description
Available Bytes available free physical memory
Pages/Sec number of pages read from or write to disk. Average value must be less than 50
Page Faults/Sec indicates the total page faults per second

Memory bottleneck resolutions

If the pages/sec count is high then check available physical memory check if system memory > 2GB, if yes then enable the advanced memory setting

image

Enabling 3GB of process space

32 bit windows operating systems are limited to use 4GB of memory.Out of 4GB , by default 2GB is reserved for operating system and remaining is allocated to application.  If you specify a /3GB switch in the boot.ini file of 32 bit OS, then OS uses only 1 GB of address space and application can access up to 3GB.

To allow SQL Server to use the 3GB of your physical memory. You can add the 3GB switch in boot.ini file as below

image

Performance Counters to analyze the disk performance

Counter Description
% Disk Time Percentage of time disk was busy. value must be less than 85%.
Disk Transfers/Sec Rate of read/write operations on disk. Maximum value less than 100 per disk
Disk Bytes/Sec Amount of data transfer to/from per disk per sec. Maximum value < 10 MB per second
Avg Disk sec/read avg value < 10 ms
Avg Disk sec/write avg value < 10 ms

Disk Bottleneck Resolutions

Common resolutions are

  • Using a RAID array
  • Using a SAN system
  • Creating multiple files and multiple file groups
  • Using Partitioned tables

Creating multiple files and multiple file groups

The data files belonging to databases can grouped together in one or more filegroups. if you add multiple files to multiple file groups across drives then your work can be done in parallel across the groups and files. You can add filegroups to your database in SQL Server SSMS as below

image

Performance Counters to analyze Processor

Counter Description
% Processor Time Percentage of time the processor busy

avg value < 80%
Batch Requests/Sec SQL Command batches received per second
SQL Compilations/Sec Number of times SQL is compiled avg value > 100

Processor Bottleneck Resolutions

  • Optimize the application performance load
  • Eliminate the excessive compiles or re-compiles
  • Running with more efficient controllers and drivers

Performance Counters to analyze Network

Counter Description
Bytes Total/Sec Rate at which bytes are transferred on Network Interface Card Avg Value must be < 50% NIC capacity
% Net Utilization % of network bandwidth in use on a network segment

avg value must be < 80% network bandwidth

Share this post :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud