System Statistical Functions in SQL Server
System statistical function in SQL returns statistical information about the system.
All the functions are listed out and described as below:
Function | Description |
---|---|
@@CONNECTIONS | returns the number of attempted connections - both successful and unsuccessful. |
Syntax
@@CONNECTIONS
Example
This example returns the count of login attempts as of the current date and time
SELECT GETDATE() AS 'Today''s Date and Time',
@@CONNECTIONS AS 'Login Attempts';
Here is the result:
Today's Date and Time Login Attempts
---------------------- --------------
22/8/2019 06:32:45 PM 224563
Function | Description |
---|---|
@@CPU_BUSY | returns the amount of time that SQL Server has spent in active operation since its latest start. @@CPU_BUSY returns a result measured in CPU time increments, or "ticks." This value is cumulative for all CPUs, so it may exceed the actual elapsed time.To convert to microseconds, multiply by @@TIMETICKS. |
Syntax
@@CPU_BUSY
SELECT @@CPU_BUSY * CAST(@@TIMETICKS AS float) AS 'CPU microseconds',
GETDATE() AS 'As of' ;
Here is the result:
CPU microseconds As of
---------------- -----------------------
18406250 2019-22-08 17:00:50.600
Function | Description |
---|---|
@@IDLE | Returns the time that SQL Server has been idle since it was last started. The result is in CPU time increments, or "ticks," and is cumulative for all CPUs, so it may exceed the actual elapsed time. Multiply by @@TIMETICKS to convert to microseconds. |
Syntax
@@IDLE
Example
The following example shows returning the number of milliseconds SQL Server was idle between the start time and the current time. To avoid arithmetic overflow when converting the value to microseconds, the example converts one of the values to the float data type.
SELECT @@IDLE * CAST(@@TIMETICKS AS float) AS 'Idle microseconds',
GETDATE() AS 'as of';
Here is the result:
I
Idle microseconds as of
----------------- ----------------------
8199934 22/8/2019 06:32:45 PM
Function | Description |
---|---|
@@IO_BUSY | Returns the time that SQL Server has spent performing input and output operations since SQL Server was last started. |
Syntax
@@IO_BUSY
Example
The following example shows returning the number of milliseconds SQL Server has spent performing input/output operations between the start time and the current time.
SELECT @@IO_BUSY*@@TIMETICKS AS 'IO microseconds',
GETDATE() AS 'as of';
Here is the result:
IO microseconds as of
--------------- ----------------------
4552312500 22/8/2019 06:32:45 PM
Function | Description |
---|---|
@@PACK_SENT | Returns the number of output packets written to the network by SQL Server since it was last started. |
Syntax
@@PACK_SENT
Example
SELECT @@PACK_SENT AS 'Pack Sent';
Here is the result:
Pack Sent
-----------
241
Function | Description |
---|---|
@@PACKET_ERRORS | Returns the number of network packet errors that have occurred on SQL Server connections since SQL Server was last started. |
Syntax
@@PACKET_ERRORS
Example
SELECT @@PACKET_ERRORS AS 'Packet Errors';
Here is the result:
Packet Errors
-------------
0
Function | Description |
---|---|
@@TIMETICKS | Returns the number of microseconds per tick. |
Syntax
@@TIMETICKS
Function | Description |
---|---|
@@TOTAL_ERRORS | Returns the number of disk write errors encountered by SQL Server since SQL Server last started. |
Syntax
@@TOTAL_ERRORS
Example
This example shows the number of errors encountered by SQL Server as of the current date and time.
SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of';
Here is the result:
Errors As of
----------- ----------------------
0 3/28/2006 12:32:11 PM
Function | Description |
---|---|
@@TOTAL_READ | Returns the number of disk reads, not cache reads, by SQL Server since SQL Server was last started. |
Syntax
@@TOTAL_READ
Example
The following example shows returning the total number of disk read and writes as of the current date and time.
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of';
Here is the result:
Reads Writes As of
----------- ----------- ----------------------
7780 96529 22/8/2019 06:32:45 PM
Function | Description |
---|---|
@@TOTAL_WRITE | Returns the number of disk writes by SQL Server since SQL Server was last started. |
Syntax
@@TOTAL_WRITE
Example
The following example shows returning the total number of disk reads and writes as of the current date and time.
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
Here is the result:
Reads Writes As of
----------- ----------- ----------------------
7780 96529 22/8/2019 06:32:45 PM
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials