Top 10 SQL Server Counters to Monitor Closely for an Industry Grade Performance

System resource contention, bad database schema, bottle-necking, and excessive time procedures or queries are the major reasons that cause performance issues of an SQL server.

SQL Server LogoSQL server is one of the mostly used databases in all kinds of industries all around the globe. It is a very important responsibility of a database administrator to maintain the performance of SQL server professionally.  The close monitoring of different critical and service affecting counters/parameters of SQL server not only increase the system up-time, efficiency and effectiveness but also chance to achieve the desired business goals of any organization.

The causes of the performance degradation of SQL server can be classified into three broad categories – bad configuration, shortage of resources and malfunctioning of different processes. Specifically speaking, we can say that the insufficient server resources, bad configuration, excessive query compilations/re-compilations, memory bottle-necking, bad execution plans and database schema designs, and CPU usage pressure can create service effecting impact on the performance of SQL server directly. A close monitoring of different parameters of SQL server can help diagnose and resolve the performance related issues very fast, and thus, help improve the performance of SQL server in an enterprise ecosystem.

The main counters of SQL – if monitored closely – can help improve its performance are given below with detailed description and their recommended range of values.

1.   Buffer Cache Hit Ratio

This is a very critical counter in all databases including SQL database. It represents the rate of how often an SQL server can find the desired data page in the buffer cache rather than going to hard disk for the same. It is recommended to maintain a higher rate above 95% for better performance of the SQL server. It is closely related with the size of memory of the server. If the buffer cache hit ratio is less than recommended range, quickly increase RAM or check for other issues because performance starts degrading rapidly.

2.   Batch Requests/Sec

The batch of requests per second counter is the number of batches handled by the SQL server in one second. It indicates how busy the SQL server CPU is. The value of this counter is arbitrary and depends on different parameters such as speed of the network link, capacity of the processor, and other server resources. Ideally, a normal SQL server with 100 Mb link can handle up to 3000 batch requests per second. So, you should monitor this counter very closely in relation with the resources of your server to get better insight.

3.   Plan Cache Hit Ratio

This is a percentile ratio of the plan-cache use. A higher ratio indicates that your server is working efficiently and effectively without creating new plans for every incoming request; the lower ratio indicates that server is struggling to do more work than it is desired to do. So, try to find out the reason immediately and resolve the issue to increase the performance. This counter should also be analyzed in the light of plan cache reuse counter for a better perspective.

4.   SQL Compilation/Sec

This counter indicates the number of times the execution plan has been compiled by the SQL server. It should always be kept as low as possible. The higher value indicates that there is a huge pressure on your server resources such as memory, processor and others. This ratio should also be compared with the batch request per second value for a deeper perspective. The rule of thumb is that each compilation should accomplish at least 10 batch requests. The higher ratio may also be indicative to the fact that adhoc queries are using resources excessively, and should be re-written those queries for a better performance.

5.   Page Life Expectancy (Sec)

Page life expectancy is the duration (in seconds) of a data page to stay in the buffer cache. The value of this counter should be longer for the better performance of an SQL server. Many experts believe that any value of this counter less than 300 seconds is not good for server performance; but this value is not a standard one too. It is an arbitrary value that depends on existing server environment. There should be a close monitoring of this parameter to maintain the performance of the server.

6.   Full Scans/Sec

The ‘full scans per second’ counter indicates the total number of scans made by server for database tables or indexes. A higher value of this counter may be the cause of missing indexes, requests for too many data records or very small data tables. Sudden increase in this value may be due to reaching of threshold value of indexes, or any other uneven condition. Meanwhile, de-fragmentation of indexes should also be done on a regular basis to improve the performance of the server.

7.   Lock Waits/Sec

This counter pertains to the management of concurrent users in the SQL environment. The number of times per second the SQL server has to wait to lock resources for the request is called lock-waits/sec. Ideally, the value of this counter should be zero, because no request should wait for resource in an industry grade performance environment of SQL server. The lock wait time counter is another useful counter that can help you understand the lock waits per second much clearer. Any increase in this counter should immediately be addressed to keep the SQL performance high.

8.   Deadlocks/Sec

This counter is closely associated with the lock waits per second. The number of lock-waits per second that resulted in the deadlocks is called the deadlocks per second. It should be kept at zero ideally, but sometimes a smaller value (less than 1 per second) for a very short period may be okay, but it lasts for longer duration, then you should take immediate action.

9.   Page Splits/Sec

SQL server splits the pages when it inserts or updates any page due to the overflowing of index pages. The number of page splits performed in a second is called page splits per second. It should be always kept low to maintain the high performance of database server because it requires huge resources for splitting a page for the insertion of data into the tables. This problem occurs due to the bad configuration of tables and indexes. To decrease page splits modify tables and indexes to lower non-sequential insertions of data into the pages. You can also use pad_index and fill factor to create more empty space.

10.   Checkpoint Pages/Sec

The dirty pages are flushed back to disk by the checkpoint operation of an SQL server. This counter measures the total number of dirty pages flushed to disk in a single second. This is an arbitrary value and depends on different parameters especially on memory. It is recommended to maintain as low value as possible of this counter. Any abrupt increase in this value is indication of pressure on the memory use. So, always monitor this SQL parameter closely to maintain high performance of the server.

An automated SQL server monitoring does not only increases performance of your SQL server but also improves the business performance of your organization by achieving great customer satisfaction. To know more about an enterprise level SQL server monitoring service, click here.

Read More

Understanding Cloud Server Provisioning & Its Advantages

A typical server provisioning includes a complete set of activities such as deciding server resources, preparing resources, implementing policies, and loading service and supporting tools to make the server operational.

cloud server  iconService provisioning is a very commonplace term used in IT and telecom sectors that means the preparation of service requested by the customer. Similarly, the server provisioning is known for accomplishing a complete set of activities required to prepare a server for the desired operation; the basic activities of server preparation include allocation of resources, loading the desired operating system, installation of desired services, testing of the services and finally making it operational for the end users. In the cloud environment, before all these activities, a comprehensive cloud policy of the company is devised that covers all aspects related to security and the location of the software – in public cloud or behind the firewall – that will be deployed on the server. In the cloud environment, the hardware machines are replaced by the Virtual Machines (VMs) and other activities remain almost same, but accomplished in different ways owing to the advanced capabilities of cloud technologies and the management platforms that govern the cloud server provisioning.

In general, cloud server provisioning can be classified into three different categories as listed below.

  • Self Provisioning
  • Dynamic Provisioning
  • Advanced Provisioning

Self Provisioning

Self provisioning is a process of server preparation that is solely dependent on the users of the cloud resources. The user purchases the cloud resources from the cloud server providers through the web interface by creating his/her account. The payment is made online for the desired server resources. All the remaining activities are required to be done by the user (customer) himself/herself. The purchased resources are normally available within a few minutes in this case.

Dynamic Provisioning

In this type of cloud server provisioning, the service providers dynamically assign more resources to the customer at the time of need and withdraw resources from the customer when they are not needed. This kind of provisioning is also known as dynamic resource provisioning on the basis of pay-per-use billing method. It is very flexible for both the providers and the customers in terms of the better utilization of resources.

Advanced Provisioning

In this kind of cloud server provisioning, a proper negotiated contract of service is prepared and signed between a provider and a customer. The service provider allocates the agreed resources and provides an API and other interfaces to the customer to access and manage the allocated resources as per business requirements. In this type of server provisioning, advanced service level agreements SLAs are agreed upon to identify the responsibilities of both parties and scope of the services.

Advantages of Cloud Server

There are many advantages of using cloud server provisioning as compared to dedicated or legacy servers, especially for small and medium size businesses. The major benefits are listed below.

  • Quick, flexible and cost efficient
  • No requirement of any upfront cost
  • Quick return on investment ROI
  • Fully managed resources
  • Easy to scale up or scale down the desired resources
  • Easy server monitoring through cloud monitoring services

It is very important to note that there are many cloud-based server monitoring services and web-based tools available on the internet that can be used for very low price or even free of charges to monitor and automate the processes of the cloud servers. This is another important benefit of using the cloud servers. To get more information about free server monitoring service click here.

Read More