Performance Counters 3

SQL Profiler

The SQL Profiler is an extremely useful tool for examining the actual SQL being run on a database and identifying candidate SQL for performance tuning.

It can also be used as an auditing tool for security purposes as it can be configured to include activities such as users logging in & out of the system; SQL being run by the users; password changes; etc. However monitoring too much activity can have a sever impact on overall system performance, thus it is better to be more selective in the choice of what events to monitor.

In regards to using SQL Profiler for performance monitoring, the following events should be selected as a minimum:

Performance
> Execution Plan
Sessions
> Existing Connection
Stored Procedures
> RPC:Starting

> RPC:Completed
> SP:Starting
> SP:Completed
> SP:StmtStarting
> SP:StmtCompleted
TSQL
> SQL:BatchStarting
> SQL:BatchCompleted
> SQL:StmtStarting
> SQL:StmtCompleted

It is also possible to filter the results further, for example you can select to filter the data where SPID is equal to an id as shown from the SP_WHO or SP_WHO2 stored procedures. This would therefore show the SQL being run by a particular connection.

The results from the SQL Profiler trace can also optionally be saved to a trace file. This is useful for keeping historical information or passing the results to the Index Tuning Wizard to identify potential indexes for performance tuning.

SQL Server Enterprise Manager

The Current Activity section within SQL Server Enterprise Manager provides output very similar to that provided by the SP_WHO & SP_WHO2 stored procedures.

You can also use this GUI to kill potential problem sessions via right clicking on the relevant process and selecting ‘kill process’.

There is also further information available regarding locks (though this information is also available via the SP_LOCK stored procedure).

Therefore it is optional to the relevant DBA whether they prefer to use the GUI or the stored procedures for returning this activity information.

Windows Performance Monitor

The Windows Performance Monitor (Perfmon or otherwise known as Sysmon) includes SQL Server specific counters that are extremely useful for monitoring database performance. However, it is also important to monitor the overall performance of the server itself including other processes / applications.

In general the following counters should be included for monitoring database, as well as overall system, performance:

Object: Memory
Counter: Page Faults / sec and Pages / sec

These counters watch the amount of paging on the system. As the system settles into a steady state, you want these values to be 0 – that is no paging occurring on the system. In fact, if you allow SQL Server to automatically adjust its memory usage (as default), it will reduce its memory resources when paging occurs. You should find that any paging that does occur is not due to SQL Server. If your system does experience regular paging, perhaps due to other applications running on the machine, you should consider adding more physical memory.

Object: PhysicalDisk
Counter: Disk Transfers / sec

This counter shows physical I/O rates for all activity on the machine. You can set up an instance for each physical disk in the system or watch it for the total of all disks. The I/O capacity of disk drives and controllers varies considerably depending on the hardware. However today’s typical SCSI hard drive can do 80 to 90 random 8-KB reads per second, assuming the controller can drive it that hard (SQL Server does most I/O in 8-KB chunks). If you see I/O rates approaching these rates per drive, you should verify whether your specific hardware could sustain more. If not, add more disks and controllers, add memory, or rework the database to try to get a higher cache-hit ratio and require less physical I/O (via better design, better indexes, possible denormalisation, and so on).

Object: PhysicalDisk
Counter: Current Disk Queue Length

This counter indicates the number of reads that are currently outstanding for a disk. Occasional spikes are ok, especially when an operation that generates a lot of asynchronous I/O, such as a checkpoint, is activated. However generally the disks should not have a lot of queued I/O. Those operations, of course, must ultimately complete, so if more than one operation is queued consistently, the disk is probably overworked. You should either decrease physical I/O or add more I/O capacity.

Object: Process
Counter: % Processor Time

Typically, you should run this counter for the SQL Server process (sqlservr) instance, however you might want to run it for other processes. It confirms that SQL Server (or some other process) is using a reasonable amount of CPU time. It doesn’t make sense to spend a lot of time reducing SQL Server’s CPU usage if some other process on the machine is using a larger percentage of the CPU to drive the total CPU near capacity).

Object: Process
Counter: Virtual Bytes

Use this counter to see the total virtual memory being used by SQL Server, especially when a large number of threads and memory are being consumed. If this value gets too high you might see ‘Out of Virtual Memory’ errors.

Object: Process
Counter: Private Bytes

This counter shows the current number of bytes allocated to a process that cannot be shared with other processes. It is probably the best counter for viewing the approximate amount of memory committed by any threads within the sqlservr.exe process space. Additional SQL Server 2000 instances will be labelled sqlservr#1, sqlservr#2, and so on.

Object: Process
Counter: Working Set

This counter shows the amount of memory recently used by a process. For a SQL Server process instance, this counter can actually be a valuable indicator of how much memory has been allocated within the SQL Server process space. Working Set is the current memory that SQL Server (and any components loaded in it) is currently accessing. It might not reflect the total amount of memory that SQL Server (and any component loaded in its process space) has allocated.

Object: Processor
Counter: %Processor Time

This counter monitors system-wide CPU usage. If you use multiple processors, you can set up an instance for each processor. Each processor’s CPU usage count should be similar. If not, you should examine other processes on the system that have only one thread and are executing on a given CPU. Ideally, your system shouldn’t consistently run with CPU usage of 80 percent or more, although short spikes of up to 100 percent are normal, even for systems with plenty of CPU capacity. If your system runs consistently above 80 percent or will grow to that level soon, or if it frequently spikes above 90 percent and stays there for 10 seconds or longer, you should try to reduce CPU usage.

First, consider making your application more efficient. High CPU usage counts can result from just one or two problematic queries. The queries might get high cache-hit ratios but still require a large amount of logical I/O. Try to rework those queries or add indexes. If the CPU usage count continues to be high, you might consider getting a faster processor or adding processors to your system. If your system is running consistently with 100 percent CPU usage, look at specific processes to see which are consuming the CPUs. It’s likely that the offending process is doing some polling or is stuck in a tight loop; if so, the application needs some work, such as adding a sleep.

Object: SQLServer:Buffer Manager
Counter: Buffer Cache Hit Ratio

There is no right value for the buffer cache-hit ratio because it is application specific. If your system has settled into a steady state, ideally you want to achieve rates of 90 percent or higher, however this is not always possible if the I/O is random. Keep adding more physical memory as long as this value continues to rise or until you run out of money.

Object: SQLServer:Databases
Counter: Log Flushes / sec

This value should be well below the capacity of the disk on which the transaction log resides. It is best to place the transaction log on a separate physical disk drive (or on a mirrored drive) so that the disk drive is always in place for the next write, since transaction log writes are sequential. There is a separate counter for each database, thus make sure you are monitoring the correct instance.

Object: SQLServer:Databases
Counter: Transactions / sec

This counter measures actual transactions – either user-defined transactions surrounded by BEGIN TRAN and COMMIT TRAN or individual data modification statements if no BEGIN TRAN has been issued. For example, if you have a batch that contains two individual INSERT statements, this counter records two transactions. The counter has a separate instance for each database and there is no method to keep track of total transactions for all of SQL Server. Use it only as a general indication of your system’s throughput. There is obviously no “correct” value, just the higher the better.

Object: SQLServer:Memory Manager
Counter: Total Server Memory (KB)

This counter can be useful, but it doesn’t reflect all memory allocated within a SQL Server process space. It reflects only memory allocated in the SQL Server buffer pool. Note that the buffer pool is not just for data pages; it is also for other memory allocations within the server, including plans for stored procedures and for ad hoc queries. Certain components can be loaded into the SQL Server process space and allocate memory that is not under SQL Server’s direct control. These include extended stored procedures, OLE Automation objects, and OLE DB provider DLLs. The memory space needed for these types of objects is included in SQL Server’s Working Set but not in the Total Server Memory counter.

Oracle Enterprise Manager