SQL Server Remote Monitoring Alerts
has developed the world's most comprehensive SQL Server alert strategy to ensure
that we know about a SQL Server problem the instant that it occurs.
The BC 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 2008, 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
Windows review - We verify your release and patch
levels for Windows. We also review SQL Server integrity
checking. We also check SQL auditing setting.
Log Review - We also examine all logs (Windows Event
Logs, SQL Server logs and SQL Agent logs) for alerts and
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 - We check and repair table
fragmentation which is a natural result of table usage that
can cause performance degradation.
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
Remote DBA monitors your server
with copyrighted technology to ensure that we know exactly what is
happening in the SQL Server database environment.
We monitor for high run queue values and track periods
when the SQL Server database server is overloaded.
Memory Overload Alert
We constantly monitor for RAM memory overloads and
provide our clients with complete reports showing time when the SQL
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)
BC Remote DBA SQL Server monitor uses leading-edge technology to monitor every
component of your SQL Server databases, and we are proud that we
routinely detect potential problems before they cause
a production outage.
We check hourly for any
new alerts in the Windows Event Logs, SQL Server logs 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.
free space in transaction log Alert - If the
transaction log directory crosses a preselected high volume
threshold value , this alert allows the
SQL Server Remote DBA to add space
to maintain a sufficient quantity of
free disk space on the server.
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 Remote DBA.
Internal SQL Server
BC monitors every
aspect of your SQL Server database, alerting the Remote DBA staff
before you experience an unplanned interruption.
alert This alert will report on any scheduled job
component has been disabled, or failed to execute
at its scheduled time.
This report will alert the Remote DBA whenever a table or index
does not have room to take another extent.
fragmentation - BC checks for all types of table
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.
log file write waits - BC monitors
for writelog waits, showing waits for write requests
to the transaction log to complete.
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.
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 Remote DBA locate files that are consuming more than a normal
proportion of I/O for redistribution.
Buffer Alert - This report alerts the Remote 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.
Sorts Alert - If disk sorts
in tempdb exceed a threshold, the alert will notify the Remote 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
contention alert -
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.
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 Remote DBA identify tables with forwarded row problems.
Contention Alert -
BC monitors RAM utilization within the buffer cache,
procedure plan, ad-hoc SQL, and log cache regions of RAM.
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.
Wait Event Alert - This query interrogates
SQL to find many important background processes such as the
lazy writer, which writes data blocks back to disk.
Waits Alert - This query interrogates
all important SQL Server event 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
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
Cursor waits are waits for asynchronous cursor
These are parallel process waits
- 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.