SQLConfigurationSQLConfiguration


Monitoring SQL  «Prev  Next»
Lesson 11 Built-in functions
Objective Monitor SQL Server with built-in functions.
SQL Server has scalar functions[1] that can be used to monitor SQL Server. While there are many different functions, the ones we are concerned with are the functions that are useful in monitoring SQL Server's behavior. The following list contains the commonly used functions and a description of what they do.
@@CONNECTIONS returns the number of attempted connections since SQL Server was last booted
@@CPU_BUSY The number of milliseconds that SQL Server was using the CPU since SQL Server started
@@IDLE The number of milliseconds that SQL Server was not working since SQL Server started
@@IO_BUSY The number of milliseconds that SQL Server was processingI/Osince SQL server started
@@MAX_CONNECTIONS The maximum number of simultaneous connections since SQL Server last started
@@PACKET_ERRORS The total number of network packet errors SQL Server had since it last started
@@PACK_RECEIVED The total number of network packets SQL Server received since it last started
@@PACK_SENT The total number of network packets SQL Server sent since it last started
@@SERVERNAME The name of the Microsoft SQL server
@@SERVICENAME The registry key that was used when starting SQL Server
@@TIMETICKS The number of milliseconds per clock tick
@@TOTAL_ERRORS The total number of disk I/O errors SQL Server had since it last started
@@TOTAL_READ The number of disk reads that SQL Server performed since it last started
@@TOTAL_WRITE The number of disk writes that SQL Server performed since it last started.
@@VERSION The version of SQL Server



All of these functions can be used in place of an expression in any SQL statement. When used, these functions return one value, and nothing you do directly affects the value returned. The simplest way to use them is in a select statement, as in the following example:
select @@total_write
For those of you who have used previous versions of SQL Server, these scalar functions that start with an @@ used to be called global variables. You will still see some documentation that refers to them that way.
The following link contains a summary of SQL Functions.
Summary Sql Functions
The next lesson reviews what you learned in this module.
[1]Scalar functions: A function that returns a single value.