 |
|
SQL Server Tips by Burleson |
Cursory Monitoring
The most practiced form of monitoring in the SQL Server and every
other database engine is probably cursory monitoring. This is a
quick-hit, light survey of the activities that are currently in
progress on the SQL Server, with the goal being to gain a basic
understanding of what is going on and to take action on any obvious
problems that are noticed.
Once it has been determined that the SQL
Server is actually available, there are three basic sets of
performance data that are of interest:
-
Session traffic, along with each
session’s associated resource usage data.
-
SQL currently being executed.
-
Global database and O/S performance
metrics.
In general, the goal is to determine who is
logged on to the SQL Server, what SQL each session is currently
running, and a global picture of resource usage on the server. For
the SQL Server, this data is very easy to come by.
If using personal performance scripts and not a third party or
Microsoft’s supplied base database monitor, a user will discover
that a quick combination of data dictionary views and one DataBase
Consistency Check (DBCC) can get what is needed. Session information
can be acquired by querying the master..sysprocesses table, with
each session’s currently executing SQL being obtained via the DBCC
INPUTBUFFER (SESSION ID) command. Global SQL Server performance
statistics can be viewed by querying the master..sysperfinfo table.
.
SQL Server 2005 also provides a number of new monitoring views,
which are all prefaced with dm_ for dynamic management, in the
master database. Upcoming chapters will provide a number of good
scripts that can be used to view all of this information. Operating
system metrics are not easily queried via SQL commands, but they can
be viewed by using a Windows supplied performance monitor as well as
getting a few O/S statistics via the new SQL Server 2005 views.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
