System Statistical Functions



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:


FunctionDescription
@@CONNECTIONSreturns 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         
FunctionDescription
@@CPU_BUSYreturns 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  
Example
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
FunctionDescription
@@IDLEReturns 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     
FunctionDescription
@@IO_BUSYReturns 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   
FunctionDescription
@@PACK_SENTReturns 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  
FunctionDescription
@@PACKET_ERRORSReturns 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  
FunctionDescription
@@TIMETICKSReturns the number of microseconds per tick.
Syntax

@@TIMETICKS  
FunctionDescription
@@TOTAL_ERRORSReturns 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  
FunctionDescription
@@TOTAL_READReturns 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 
FunctionDescription
@@TOTAL_WRITEReturns 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 


Connection String

SQL Server Connection String Formats

posted on 2019-08-27 05:53:53 - SQL Server Tutorials


UNION

union in SQL server

posted on 2019-08-09 23:05:33 - SQL Server Tutorials


System Functions

System Functions in SQL Server

posted on 2019-08-09 06:14:33 - SQL Server Tutorials


Prompt Examples

ChatGPT Prompt Examples

posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


Use Cases

Chat GPT Key Use Cases

posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


Prompt Frameworks

Prompt Frameworks

posted on 2023-06-21 19:33:06 - ChatGPT Tutorials