Microsoft® SQL Server® 2012 Bible

912

Part VII: Monitoring and Auditing

Object Counter Description Usefulness

SQLServer:

Locks

Average wait

time (in millisec-

onds), lock waits,

and lock time-

outs per second

A cause of serious per-

formance problems;

lock waits, the length of

the wait, and the num-

ber of lock timeouts are

all good indicators of

the level of locking con-

tention within a

database.

If locking issues are

detected, the indexing

structure and transaction

code should be examined.

SQLServer: User

Connections

User

connections

Number of current

connections.

Indicates potential

database activity.

SQLServer:

Databases

Transactions per

second

Number of current

transactions within a

database.

A good indicator of data-

base activity.

The “best counter” list seems to change with every new article Paul Randal writes. Read

the blogs, experiment, and keep track of the ones you fi nd meaningful. To get your

research off on the right foot, you can fi nd a great article at SQLSkills.com by Paul Randal

about performance tuning considerations at http://www.sqlskills.com/BLOGS/PAUL/post/

Important-considerations-when-performance-tuning.aspx.

In addition, the SQL Server: Wait Statistics counters are useful windows into

potential SQL Server bottlenecks; a number of interesting memory counters are in

SQL Server: Resource Pool Stats.

A complete list of SQL Server counters and their current values can be queried from the sys.dm_os_

performance_counters dynamic management view. This is cool, because you can get the counter data using

Transact-SQL code. If you want to make retrieving this information even cooler, check out the section “Accessing

Performance Counters with PowerShell” later in this chapter.

You can create custom counters using stored procedures sp_user_counter1 through

sp_user_counter10 to pass data from your database code to System Monitor. This can be

useful to show the number of transactions processed by a performance test or the number

of rows inserted by a data generator. There are ten possible user counters. The following

trivial example increments one of the counters:

DECLARE @Counter int

SET @Counter = 0

While @Counter < 100

TABLE 37-1 (continued)

c37.indd 912c37.indd 912 7/31/2012 10:03:53 AM7/31/2012 10:03:53 AM

http://www.it-ebooks.info