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.

|