Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Robin Schumacher

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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter