|
|
Sentry-go®
3Ds (UK) Limited
© 2000-2010 |
|
 |
|
|
|
 |
Monitoring SQL Server Locking |
|
|
|
|
To implement secure concurrent access, database systems such as SQL Server
implement a complex locking mechanism. Monitoring SQL Server locks for blocking
can be critical to efficient use of databases.
If not checked & resolved quickly, "blocking" as it's known can be very serious
& can soon bring any system relying on the underlying data to it's knees.
|
|
|
 |
Windows applications often use SQL Server as their database of choice. SQL
server is a relational database like many others, holding its data in a series
of tables, with indexes & keys helping to provide fast, efficient access.
To help maintain secure & concurrent access, database systems such as SQL Server
use a complex locking mechanism.
Whilst this is extremely efficient at
protecting data and ensuring changes are applied in the correct sequence etc.,
it can mean that one user or process can easily prevent another from continuing
simply because it has locked a resource the other needs.
|
|
|
In well designed, efficient databases, with well written SQL and good
client-side access, locking problems should be kept to a minimum. Inevitably,
some contention problems will occur from time to time but they should be so
quick that the end user wouldn't notice. However, in poorly designed,
overstretched or poorly performing systems blocking can be an extremely
important factor, both in terms of speed & data delivery for all connected
users. Locking errors can also cause other secondary errors, such as the
transaction log file to grow excessively and even fill up, so it is extremely
important to detect issues early, before the problem gets out of hand.
|
|
|
|
|
|
An example of how Process A is preventing
(blocking) Process B from continuing |
|
|
Monitoring SQL Server locks manually takes time and often involves the running
of utilities such as DBCC, Enterprise Manager & investigating the database's
transaction log - all of which takes time and is usually instigated only after
long delays or end-users report errors. With Sentry-go, monitoring SQL Server
locking within databases, tables & indexes is quick, easy and automatic! If locks are held
for excessive time, or processes/queries are blocked, information such as the
calling user, database & SQL can be recorded for further analysis and alerts
raised, informing the database administrator (DBA) of the problem.
Alternatively, blocking or blocked connections can automatically be terminated,
without the need for manual intervention, thus releasing resources and allowing
other SQL & processes to continue.
When monitoring SQL Server locking, you can,
for example ...
- Monitor the overall SQL Server locking system
- Notify the DBA/Administrator(s) when blocking occurs
- Optionally terminate transactions blocking others
- Optionally terminate transactions being blocked
- Optionally record blocking incidents for further investigation
SQL Server lock monitoring would be of benefit with any business critical
system, application or web site that relies on a SQL Server database for its
data store.
|
|
|
|
|