Performance Monitoring of SQL Server 2000(CRM)

Performance Monitoring of SQL Server 2000
Microsoft CRM depends heavily on Microsoft SQL Server 2000. You should ensure that you measure the Windows 2000
and Windows Server 2003 counters previously mentioned, but also the SQL Server counters. As a matter of course, you
should monitor the following settings on SQL Server.
Object Counter Comments
SQLServer:Access
Methods
Full Scans/sec When the number of full scans is significantly
greater than a baseline comparison, it may indicate
index statistics are out of date.
SQLServer:Buffer
Manager
Buffer Cache Hit Ratio If this value is less than 80 percent, your system may
need additional memory resource for SQL Server.
Ideally this value is at or near 100 percent. When
this percentage is near 100 percent, your server is
operating at optimal efficiency (as far as disk I/O is
concerned).
SQLServer:Databases Log Growths (run against
your application database
instance)
Log files growing during times of heavy system
usage will result in poor performance.
SQLServer:Databases
Application Database
Percent Log Used (run
against your application
database instance)
If the percentage of log space used approaches 100
percent, transaction log backups should be
performed more often or the transaction log files
should be increased in size.
SQLServer:Databases
Application Database
Transactions/sec (run
against your application
database instance)
The number of transactions started for the database.
SQLServer:Locks Lock Waits/sec Although blocking locks are inevitable, a value
significantly greater than a baseline comparison for
an extended period of time indicates a performance
penalty due to blocking locks. Blocking locks occur
when read operations block writes, writes block
reads, or writes block other writes.
SQLServer:Locks Number of Deadlocks/sec Although deadlocks are inevitable, a value
significantly greater than a baseline comparison for
an extended period of time indicates a performance
bottleneck. Deadlocks occur when operations each
want a resource the other has locked. If the
operations both involve writes, SQL Server must
choose one of the transactions and roll it back in
order for the other transaction to proceed. The undo
and subsequent re-do operation are the causes of less
than optimal performance.
SQLServer:Memory
Manager
Memory Grants Pending Defined as the current number of processes waiting
for a workspace memory grant. This counter, along
with Buffer Cache Hit Ratio can confirm a memory
resource bottleneck.