sp_monitor

The sp_monitor system stored procedure shows you statistics about your SQL Server. You must be a member of the sysadmin role to run the procedure. The procedure provides an interesting way to perform trend analysis on your server by creating a table to log the data over time and a SQL Server agent job to execute your procedure.

The last_run column shows you the last time the sp_monitor stored procedure was run, and the current_run column shows you the current system time. The seconds column displays how many seconds ago the stored procedure was run. Some sample results are shown in the following table.

last_run

current_run

Seconds

2001-01-20 22:14:36.177

2001-01-20 22:15:24.697

48

Each time you restart SQL Server, the three columns are reset.

The rest of the columns show general statistics about SQL Server. The columns are represented as number(number)percentage. The first number represents the amount of activity since SQL Server started, and the number in parentheses tells you how much activity has occurred since the last time sp_monitor was run. The percentage is the amount of activity as a percentage since startup. For example, in the following table, you can see the second part of a report:

cpu_busy

io_busy

Idle

7(0)-0%

1(0)-0%

92693(46)-95%

The results show the idle column value of 92693 (about 25 hours), and also show that the server has been idle for 46 seconds since the last time sp_monitor was run. The percentage report indicates that since SQL Server started, the server has been idle 95 percent of the time. This server could obviously handle quite a bit more load.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net