Archive for bottelneck

SQL Server Performance Monitor Counters

Posted in SQL Server with tags , , , , , on January 1, 2012 by DBAWork

As we are aware SQL server has certain counters which can be monitored to have better performance using SQL server. We will try to elaborate on all counters. These are counters we need to monitor on regular basis.

We will see what is meaning of Batch Requests/Sec counter, how to read it from SQL server.

To get a feel of how eventful SQL Server is, observer this SQLServer counter. This counter processes the number of batch demands that SQL Server receives per second, and largely follows in step to how hectic your server’s CPUs are. Usually speaking, over 1000 batch requests per second specifies a very eventful SQL Server, and could mean that if you are not already suffering a CPU bottleneck, that server may very well soon. Unquestionably, this is a comparative number, and the higher your server hardware, the additional batch requests per second SQL Server can serve.

From a network traffic jam approach, a classic 100 Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this demanding, you may need to have two or more network cards, or go to a Gbs network card.

Several DBAs use the Transaction/Sec Total to measure total SQL Server commotion, but this is  a bad idea. Transaction/Sec only processes action that is inside a transaction, not all movement, producing crooked results. In its place, continuously use the SQLServer: SQL Statistics: Batch Requests/Sec counter, which events all SQL Server commotion.

One by one we will see all these counters

DBAWorkDatabase support, Database services, Oracle ADF