n order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.
In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.
As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
sp_who2
Or you can append the 'active' parameter and SQL will return only the active connections:
sp_who2 active
An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.
As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:
select * FROM sys.sysprocesses
The result will mainly contain the following information:
But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:
SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc
The output will be grouped by database ID and then by username, for a simpler reading.
If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').n order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.
In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.
As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
sp_who2
Or you can append the 'active' parameter and SQL will return only the active connections:
sp_who2 active
An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.
As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:
select * FROM sys.sysprocesses
The result will mainly contain the following information:
But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:
SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc
The output will be grouped by database ID and then by username, for a simpler reading.
If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').
In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.
As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
- SPID: System process id that requested the lock
- STATUS: Background, sleeping or runnable
- LOGIN: The login name that has requested the lock
- HOSTNAME: The computer where the lock request has been initiated
- BLKBY: The spid of the connection that is blocking the current connection
- DBNAME: The database name where the lock request has been generated
- COMMAND: General command type that requested the lock
- CPUTIME: The number of milliseconds the request has used
- DISKIO: Disk input / output that the command has used
- LASTBATCH: Date and time of the last batch executed by the connection
- PROGRAMNAME: The name of the application that issued the connection
sp_who2
Or you can append the 'active' parameter and SQL will return only the active connections:
sp_who2 active
An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.
As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:
select * FROM sys.sysprocesses
The result will mainly contain the following information:
- SPID: SQL Server session ID.
- KPID: Windows thread ID.
- BLOCKED: ID of the session that is blocking the request. If this column is NULL, the request is not blocked
- DBID: ID of the database currently being used by the process.
- UID: ID of the user that executed the command.
- CPU: Cumulative CPU time for the process.
- PROGRAM_NAME: Name of the application program.
- CMD: Command currently being executed.
- NT_USERNAME: Windows user name for the process, if using Windows Authentication, or a trusted connection.
- LOGINAME: Login name
But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:
SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc
The output will be grouped by database ID and then by username, for a simpler reading.
If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').n order to solve performance issues or to check database usage, it can be quite useful to know how to get current connections to a SQL Server.
In SQL Server 2000 (if you still have one...), SQL Server 2005 and SQL Server 2008 the sp_who2 stored procedure returns information about current SQL Server 2000 users and processes. This function is unfortunately not very well documented.
As a general information, you must know that the connections returned by this function are denoted as SPID, or Server process Id. Running sp_who2 is easy, All that is required is to type sp_who2 and type F5.
Note that the first 50 results are system SPIDs (Generally these do not impact the performance of the system). So, Sp_who2 gives you this information:
- SPID: System process id that requested the lock
- STATUS: Background, sleeping or runnable
- LOGIN: The login name that has requested the lock
- HOSTNAME: The computer where the lock request has been initiated
- BLKBY: The spid of the connection that is blocking the current connection
- DBNAME: The database name where the lock request has been generated
- COMMAND: General command type that requested the lock
- CPUTIME: The number of milliseconds the request has used
- DISKIO: Disk input / output that the command has used
- LASTBATCH: Date and time of the last batch executed by the connection
- PROGRAMNAME: The name of the application that issued the connection
sp_who2
Or you can append the 'active' parameter and SQL will return only the active connections:
sp_who2 active
An important thing to remember when runnign sp_who2 is that the user running the query must have the VIEW SERVER STATE permission on the server in order to see all executing sessions on the instance of SQL Server. Otherwise, the user will see just the current session.
As an alternative, which is still working even if deprecated under SQL 2008, the system table sys.sysprocesses contains connection information for each connection made to the SQL Server. You can query it like this:
select * FROM sys.sysprocesses
The result will mainly contain the following information:
- SPID: SQL Server session ID.
- KPID: Windows thread ID.
- BLOCKED: ID of the session that is blocking the request. If this column is NULL, the request is not blocked
- DBID: ID of the database currently being used by the process.
- UID: ID of the user that executed the command.
- CPU: Cumulative CPU time for the process.
- PROGRAM_NAME: Name of the application program.
- CMD: Command currently being executed.
- NT_USERNAME: Windows user name for the process, if using Windows Authentication, or a trusted connection.
- LOGINAME: Login name
But, IMHO, for a better understanding of your SQL Server usage, this queryis the best one:
SELECT db_name(dbid) as Database_Name, count(dbid) as Connections,
loginame as Login_Name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
order by Connections desc
The output will be grouped by database ID and then by username, for a simpler reading.
If you are not sure of which SQL Server version you have, run this query:select SERVERPROPERTY('ProductVersion').
No comments:
Post a Comment