SQL Server Remote Monitoring Alerts

BC has developed the worlds most comprehensive SQL Server alert strategy to ensure that we know about a SQL Server problem the instant that it occurs.  The BEI response center will often locate and correct any SQL Server problem before your end-users experience a delay or outage.

Initial SQL Server health Check

When you start your contract for SQL Server remote DBA, we perform an exhaustive check of all SQL Server components to ensure that your SQL Server database follows industry best practices:

I/O distribution analysis - I/O details for databases and files are obtained, and for SQL Server 2005, we drill down and get I/O distribution at the object level.  WE check I/O distribution using a real workload and find likely candidates for physical disk relocation or redistribution.

Hardware review - We review your complete external hardware to identify potential bottlenecks and areas of contention. This step examines disk I/O latency (seeking disk bottlenecks) and a cursory review of Network, RAM and CPU utilization.  For RAM, if pages/sec consistently high, this indicates paging and swapping activity

Windows review - We verify your release and patch levels for Windows. We also review SQL Server integrity checking (if installed). We also check SQL auditing setting.

Log Review - We also examine all logs (Windows Event Logs, SQL Server long and SQL Agent logs) for alerts and important messages.

Transaction log inspection - We review the transaction log for optimal size and bottlenecks.

TEMP space review - We review tempdb to ensure optimal sizing (disk sorts).

Table pinning review - BC identifies heavily accessed objects that can be improved by placing them in their own filegroups, assuming separate I/O controllers exist for distinct hard drives, the best place for objects to reside will always be memory.

Table fragmentation check - Fragmentation is the natural result of table usage, and reorganization of correct fragmentation issues.

Server Configuration Options check - BC verifies that all SQL Server configuration parameters are optimized for the system workload.

Review Scheduled tasks - Review users and document all auto-start and SQL services options

Windows Server Alerts

BC remote DBA monitors your server with copyrighted technology to ensure that we know exactly what is happening in the MSSQL database environment.

CPU Overload Alert – We monitor for high run queue values and track periods when the Oracle database server is overloaded.  CPU usage, if it is over 90% then it will check for the table that will store the percentage of CPU usage.

RAM Memory Overload Alert – We constantly monitor for RAM memory overloads and provide our clients with complete reports showing time when the Oracle database server experiences shortages of RAM memory. 

Network Latency Alert – This alert fires when network latency (as measured by networkio) detects excessive network times.

External SQL Server Database Alerts (Windows)

The BEI Remote DBA SQL Server monitor uses leading-edge technology to monitor every component of your SQL database, and we are proud that we detect potential problems before they cause a production outage.

Log Alerts – We check hourly for any new alerts in the Windows Event Logs, SQL Server long and SQL Agent logs, and e-mail the dumps directly to our support center.  We monitor for writelog waits, showing waits for write requests to the transaction log to complete.

Low free space in transaction log Alert - If the transaction log directory become full, this alert allows the SQL Server DBA to add space before the database hangs.

Directory space Alert - The script checks all directories containing SQL Server files.  For files where autogrowth is enabled we check whether any database or log is nearing its maximum file size limit. and send an e-mail alert to the on-call DBA.

Internal SQL Server Database Alerts

BC monitors every aspect of your SQL Server database, alerting the DBA staff before you experience an unplanned interruption.

Broken Job alert – This alert will report on any scheduled job component has been disabled, or failed to execute at its scheduled time.

Object cannot extend Alert - This report will alert the DBA whenever a table or index does not have room to take another extent.

Object fragmentation - BC checks for all types of table fragmentation:

Extent Scan Fragmentation: This highlights any gaps or lack of contiguous order in extents and indicates the presence of external fragmentation. Percentages of 25-50% or more are not a favorable reading.

Logical Scan Fragmentation: This metric represents the percentage of pages that are out of physical order. Values greater than 25% for this metric may mean that index scan performance is not what it could be. This statistic is meaningless for tables without clustered indexes (heap tables).

Average Page Density: This metric represents the fullness of the data or index pages, and is an indicator of internal fragmentation.
 

Tablespace > 95% free Alert - This report sends an e-mail alert whenever any tablespaces contain less space than specified.

Transaction log file write waits - BC monitors for writelog waits, showing waits for write requests to the transaction log to complete.

Hot File Reads Alert - This script reports on files whose reads are greater than (25 percent or 50 percent or 75 percent) of total SQL Server reads.

Hot File Writes Alert - This script alerts you to files whose write I/Os are greater than (25 percent or 50 percent or 75 percent) of total writes. This information can help the DBA locate files that are consuming more than a normal proportion of I/O for redistribution.

Data Buffer Alert - This report alerts the DBA to times when the data buffer utilization exceeds its preset threshold.   Page life expectancy readings of 300 seconds or less often indicate too many table scans are occurring which is an activity that can flood the buffer cache with pages used only once or seldom, at best.

Disk Sorts Alert - If disk sorts in tempdb exceed a threshold, the alert will notify the DBA.

Session Wait Alert - Whenever you see session waits, you have a condition where a data block is in the data buffer but is unavailable. SQL Server will record the wait type and wait time in two of the columns in master.dbo.sysprocesses.

Latch contention alert - Excessive latching operations occur during row transfers to memory and for controlling modifications to row offset table, and we monitor this with the networkio metric.

Low file space and Log Space Alert - The sp_spaceused procedure used for object and database space does not really yield the whole space picture for an object or database, but BC has a special procedure to accurately alert for all space alerts.

Forwarded Row Alert - At times, forwarded records can reduce performance because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself.  The BC fragmentation diagnostic procedures supplied will help the DBA identify tables with forwarded row problems.

RAM Contention Alert - BC monitors RAM utilization within the buffer cache , procedure plan, ad-hoc SQL, and log cache regions of RAM.

Full Table Scan Alert - Long-table full table scans are only legitimate when the query requires access a large percentage of the SQL table rows.  BC identifies SCAN operations that show up in the EXPLAIN plans versus SEEKs. SEEKs attempt to go directly to the rows necessary to fulfill a request; whereas scans read the whole object.

Background Wait Event Alert - This query interrogates SQL to find many important background process such as the lazy writer, which writes data blocks back to disk.

System Waits Alert - This query interrogates all important SQL Serverevent structures to locate events where there are excessive waits:

• ums_thread - BC seeks batch jobs waiting for a worker thread to free up, or a batch waiting to get a worker thread.
   

• async_diskpool_lock – This can indicate a disk bottleneck during backup operations.
   

• i/o_completion and async_i/o_completion – These are waits for asynchronous I/O requests to complete.
   

• cursor – Cursor waits are waits for asynchronous cursor threads.
   

• cxpacket – These are parallel process waits
   

• latch_x - BC also examines internal match waits

BC is the leader in professional SQL Server remote DBA monitoring for all mission-critical systems.  Don't trust your SQL Server to a beginner, use seasoned experts.