Monitor Microsoft SQL Server™ for higher performance and availability with WildMetrix™
Maximize SQL Server performance, availability, and reliability with WildMetrix SQL Server monitoring solutions.
Microsoft SQL Server™ is a highly utilized application which many distributed
applications rely on for handling data storage. Performance and availability of
Microsoft SQL Server™ is pivotal in the success of distributed applications which rely
on it. Each application using Microsoft SQL Server™ can have different requirements and
have different effects on the performance of Microsoft SQL Server™ as a whole and
therefore tools which engage proactive response and resolution to incidents in real-time
improve overall efficiency of dependent applications.
Benefits
of Monitoring Microsoft SQL Server with WildMetrix
Helps administrators detect problems in their SQL Server
architecture
Quickly diagnose exactly where and what the problems are
Resolves the problems from a single easy-to-use interface
Gain understanding of entire SQL Server system inside and out
SQL Server's architecture has many internal
components and objects. It is highly dependent on
the hardware configuration of SQL Server
Architecture includes:
Memory Manager dynamically acquires and frees memory as needed.
Query Processor accepts, interprets, and executes SQL statements.
Buffering uses virtual memory to a buffer cache and uses the cache to reduce physical I/O.
Transaction Architecture manages Inserts, Selects, Deletes of data.
Locking can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases.
Monitoring SQL Server complexity requires continually looking
at all elements involved to know and understand if health and
performance is optimal. Each SQL Server aspect is important
including:
Connections & Queues
Memory and Buffer Utilization
Database and Log Sizes and Utilization
SQL Query Availability and Response
Process Utilization
Services states and events
Eventlog Errors, Warnings, and Informational Events
Underlying Platform Performance
Dependent Infrastructure Performance
Customizable Microsoft SQL 2005 Server Dashboards
Live performance dashboards give you the performance metrics,
and events of Microsoft SQL 2005 Server in a single view with quick link
access to powerful analytical charting. Dashboards include:
SQL 2005 Server Access and Statistics
SQL 2005 Server Buffer Manager
SQL 2005 Server Cache Manager
SQL 2005 Server Database
SQL 2005 Server Latches and Locks
SQL 2005 Server Memory Manager
SQL 2005 Server Performance Explorer
SQL 2005 Server Summary
Powerful Analytics
The tools make the difference when finding the root-cause of
performance bottlenecks, application faults, and performing trend
analysis. Analytical Charting features include:
Correlation of Live or Historical Data
Drill-Down, Zoom, and Statistic Lines
OpenGL 3D Surface Analysis
Live, Second, Minute, Hourly, and Monthly Data Periods
Powerful Monitoring Solutions
Deployment of pre-built monitoring solutions is as simple as
Drag-n-Drop. Policies are customizable by adding performance
counters and setting your desired collection and retention intervals
as well as alerting rules.
The Microsoft SQL Server monitoring policy includes:
(click nodes to drill down)
WildMetrix Monitoring Policies
Microsoft SQL 2005 Server
Access and Statistics
\SQLServer:Access Methods\Full Scans/sec
Number of unrestricted full scans. These can either be base table or full index scans.
\SQLServer:Access Methods\Index Searches/sec
Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index.
\SQLServer:Access Methods\Page Splits/sec
Number of page splits occurring as the result of index pages overflowing.
The number of times locks on a table were escalated.
\SQLServer:General Statistics\User Connections
Number of users connected to the system.
\SQLServer:SQL Statistics\Batch Requests/sec
Number of SQL batch requests received by server.
\SQLServer:SQL Statistics\SQL Compilations/sec
Number of SQL compilations.
SQL Server High Compilations
Buffer Manager
\SQLServer:Buffer Manager\Buffer cache hit ratio
Percentage of pages that were found in the buffer pool without having to incur a read from disk.
The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.
\SQLServer:Buffer Manager\Checkpoint pages/sec
Number of pages flushed by checkpoint or other operations that require all dirty pages to be flushed. This is a normal procedure and will cause this counter to rise during the checkpoint process. What you don't want to see is a high value for this counter over time.
\SQLServer:Buffer Manager\Database pages
Number of pages in the buffer pool with database content.
\SQLServer:Buffer Manager\Free pages
Total number of pages on all free lists.
\SQLServer:Buffer Manager\Lazy writes/sec
Number of buffers written by buffer manager's lazy writer.
The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
\SQLServer:Buffer Manager\Page life expectancy
Number of seconds a page will stay in the buffer pool without references.
Low SQLServer:Buffer Manager Page Life Expectancy
\SQLServer:Buffer Manager\Page reads/sec
Number of physical database page reads issued.
This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.
\SQLServer:Buffer Manager\Stolen pages
Number of pages used for miscellaneous server purposes (including procedure cache).
\SQLServer:Buffer Manager\Total pages
Number of pages in the buffer pool (includes database, free, and stolen).
Cache Manager
\SQLServer:Plan Cache()\Cache Hit Ratio
Ratio between cache hits and lookups
\SQLServer:Plan Cache()\Cache Object Counts
Number of cache objects in the cache
\SQLServer:Plan Cache()\Cache Objects in use
Number of cache objects in use
\SQLServer:Plan Cache()\Cache Pages
Number of 8k pages used by cache objects
\SQLServer:Plan Cache(_Total)\Cache Hit Ratio
Ratio between cache hits and lookups
\SQLServer:Plan Cache(_Total)\Cache Object Counts
Number of cache objects in the cache
\SQLServer:Plan Cache(_Total)\Cache Objects in use
Number of cache objects in use
\SQLServer:Plan Cache(_Total)\Cache Pages
Number of 8k pages used by cache objects
Database
\SQLServer:Databases()\Data File(s) Size (KB)
The cumulative size of all the data files in the database.
\SQLServer:Databases()\Log Cache Hit Ratio
Percentage of log cache reads that were satisfied from the log cache.
\SQLServer:Databases()\Log Cache Reads/sec
Reads performed through the log manager cache.
\SQLServer:Databases()\Log File(s) Size (KB)
The cumulative size of all the log files in the database.
\SQLServer:Databases()\Log Flush Wait Time
Total wait time (milliseconds).
\SQLServer:Databases()\Log Flush Waits/sec
Number of commits waiting on log flush.
\SQLServer:Databases()\Log Flushes/sec
Number of log flushes.
\SQLServer:Databases()\Log Growths
Total number of log growths for this database.
\SQLServer:Databases()\Log Shrinks
Total number of log shrinks for this database.
\SQLServer:Databases()\Log Truncations
Total number of log truncations for this database.
The cumulative size of all the log files in the database.
\SQLServer:Databases(_Total)\Log Flush Wait Time
Total wait time (milliseconds).
\SQLServer:Databases(_Total)\Log Flush Waits/sec
Number of commits waiting on log flush.
\SQLServer:Databases(_Total)\Log Flushes/sec
Number of log flushes.
\SQLServer:Databases(_Total)\Log Growths
Total number of log growths for this database.
\SQLServer:Databases(_Total)\Log Shrinks
Total number of log shrinks for this database.
\SQLServer:Databases(_Total)\Log Truncations
Total number of log truncations for this database.
\SQLServer:Databases(_Total)\Percent Log Used
The percent of space in the log that is in use.
\SQLServer:Databases(_Total)\Transactions/sec
Number of transactions started for the database.
Latches and Locks
\SQLServer:Latches\Average Latch Wait Time (ms)
Average latch wait time (milliseconds) for latch requests that had to wait.
\SQLServer:Latches\Latch Waits/sec
Number of latch requests that could not be granted immediately and had to wait before being granted.
\SQLServer:Latches\Total Latch Wait Time (ms)
Total latch wait time (milliseconds) for latch requests that had to wait in the last second.
\SQLServer:Locks()\Average Wait Time (ms)
The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
\SQLServer:Locks()\Lock Requests/sec
Number of new locks and lock conversions requested from the lock manager.
\SQLServer:Locks()\Lock Timeouts/sec
Number of lock requests that timed out. This includes internal requests for NOWAIT locks.
\SQLServer:Locks()\Lock Wait Time (ms)
Total wait time (milliseconds) for locks in the last second.
\SQLServer:Locks()\Lock Waits/sec
Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
\SQLServer:Locks()\Number of Deadlocks/sec
Number of lock requests that resulted in a deadlock.
\SQLServer:Locks(_Total)\Average Wait Time (ms)
The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
High SQLServer:Locks Average Wait Time
\SQLServer:Locks(_Total)\Lock Requests/sec
Number of new locks and lock conversions requested from the lock manager.
\SQLServer:Locks(_Total)\Lock Timeouts/sec
Number of lock requests that timed out. This includes internal requests for NOWAIT locks.
\SQLServer:Locks(_Total)\Lock Wait Time (ms)
\SQLServer:Locks(_Total)\Lock Waits/sec
Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
\SQLServer:Locks(_Total)\Number of Deadlocks/sec
Number of lock requests that resulted in a deadlock.
SQLServer:Deadlocks
Logging
\Extended:Server\EventLog
The EventLog extended counter is a real-time provider of event log data. The counter captures all events from the event logs.
MS SQLServer Error
MS SQLServer Warning
\Extended:Server\ServiceLog
The ServiceLog extended counter is a query to check for status changes of registered services. Status changes include Running, Stopping, Stopped, and Starting.
Distributed Transaction Coordinator has Started
Distributed Transaction Coordinator has Stopped
Microsoft Search has Started
Microsoft Search has Stopped
MSSQLServerADHelper Service has Started
MSSQLServerADHelper Service has Stopped
SQL Server Agent has started
SQL Server Agent has stopped
SQL Server Service has stopped
SQLServer Service has started
Memory Manager
\SQLServer:Memory Manager\Connection Memory (KB)
\SQLServer:Memory Manager\Lock Memory (KB)
Total amount of dynamic memory the server is using for locks
\SQLServer:Memory Manager\SQL Cache Memory (KB)
Total amount of dynamic memory the server is using for the dynamic SQL cache
\SQLServer:Memory Manager\Target Server Memory(KB)
Total amount of dynamic memory the server is willing to consume
\SQLServer:Memory Manager\Total Server Memory (KB)
Total amount of dynamic memory the server is currently consuming
Process
\Process(sqlservr)\% Processor Time
% Processor Time is the percentage of elapsed time that all of the threads of this process used the processor to execute instructions. An instruction is the basic unit of execution in a computer, a thread is the object that executes instructions, and a process is the object created when a program is run. Code executed to handle some hardware interrupts and trap conditions are included in this count. On Multi-processor machines the maximum value of the counter is 100 % times the number of processors.
\Process(sqlservr)\Handle Count
The total number of handles currently open by this process. This number is equal to the sum of the handles currently open by each thread in this process.
\Process(sqlservr)\IO Read Bytes/sec
The rate at which the process is reading bytes from I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.
\Process(sqlservr)\IO Write Bytes/sec
The rate at which the process is writing bytes to I/O operations. This counter counts all I/O activity generated by the process to include file, network and device I/Os.
\Process(sqlservr)\Page Faults/sec
Page Faults/sec is the rate at which page faults by the threads executing in this process are occurring. A page fault occurs when a thread refers to a virtual memory page that is not in its working set in main memory. This may not cause the page to be fetched from disk if it is on the standby list and hence already in main memory, or if it is in use by another process with whom the page is shared.
\Process(sqlservr)\Private Bytes
Private Bytes is the current size, in bytes, of memory that this process has allocated that cannot be shared with other processes.
\Process(sqlservr)\Thread Count
The number of threads currently active in this process. An instruction is the basic unit of execution in a processor, and a thread is the object that executes instructions. Every running process has at least one thread.
\Process(sqlservr)\Virtual Bytes
Virtual Bytes is the current size, in bytes, of the virtual address space the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and the process can limit its ability to load libraries.
\Process(sqlservr)\Working Set
Working Set is the current number of bytes in the Working Set of this process. The Working Set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a threshold, pages are left in the Working Set of a process even if they are not in use. When free memory falls below a threshold, pages are trimmed from Working Sets. If they are needed they will then be soft-faulted back into the Working Set before they leave main memory.
Microsoft SQL Server™ is a highly utilized application which many distributed
applications rely on for handling data storage. Performance and availability of
Microsoft SQL Server™ is pivotal in the success of distributed applications which rely
on it. Each application using Microsoft SQL Server™ can have different requirements and
have different effects on the performance of Microsoft SQL Server™ as a whole and
therefore tools which engage proactive response and resolution to incidents in real-time
improve overall efficiency of dependent applications.
Ascendview,
Copyright © 2003-2008