SQL Server gets the SPID, the only SessionID

Like a session in. NET, if you know the SessionID in the database, all operations are known because of this unique identity.

There are many things you can do, such as: What user is doing, what SQL is being executed, and how many stored procedures are executed separately in a larger logic.

In the process of executing these stored procedures, you want to know the progress of the current execution, which SQL SQL statement is being executed, then it is easy to pass SessionID

To get this information.

SQL Server gets the SPID, the only SessionID:

SELECT @ @SPID

I have not known before, recently installed SQLServer2014, found that each open a query interface has an ID out. I just want to know how to take SessionID.

The following stored procedure is used to see which SessionID are performing what operations.

Create PROC [dbo]. [Dba_whatsqlisexecuting]
As

BEGIN
--Does not lock anything, and does not get held to any locks.
SET TRANSACTION Isolation Level READ UNCOMMITTED


-What SQL statements is currently Running?
SELECT [Spid] = session_id
, ecid
, [Database] = db_name (sp.dbid)
, [User] = Nt_username
, [Status] = Er.status
, [Wait] = Wait_type
, [individual Query] = SUBSTRING (Qt.text,
ER.STATEMENT_START_OFFSET/2,
(case when er.statement_end_offset =-1
Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2
ELSE Er.statement_end_offset End-
Er.statement_start_offset)/2)
, [Parent Query] = Qt.text
, program = Program_name
, Hostname
, Nt_domain
, start_time
From sys.dm_exec_requests ER
INNER JOIN sys.sysprocesses sp on er.session_id = Sp.spid
Cross APPLY Sys.dm_exec_sql_text (er.sql_handle) as Qt
WHERE session_id >-Ignore system SPIDs.
and session_id not in (@ @SPID)--Ignore the current statement.
--and db_name (sp.dbid) = ' Rangechecktool '
ORDER by 1, 2
END

You can also refer to the following article:

http://www.mssqltips.com/sqlservertip/1799/identify-last-statement-run-for-a-specific-sql-server-session/

Identify Last statement run for a specific SQL Server session

Problem
I was reading a recent blog post from Pinal Dave, SQL Server MVP, regarding returning information on the latest query exec  Uted for a given session. He offered up a couple options to return the last query statement executed, settling upon querying the sys.sysprocesses sy  Stem compatibility View, but another-on-the-this can-be-done was through the Dynamic Management views and Functions. The process for doing are quite straight-forward and works in all versions of Microsoft SQL Server since DMOs (dynamic m Anagement objects) were integrated into SQL Server.

solution
Before proceeding we should take a second to explain what a session is.  in Microsoft SQL Server, a session is Synony MOUs with a user process.  Previous to SQL 2005 sessions were referred To-and identified solely-as SPIDs (short F or session ID) .  A SPID uniquely identifies a session and a SPID is unique across the SQL Server instance.  in A n attempt to conform SQL Server object identifiers to is more user-friendly and to standardize a naming convention across All system objects, sessions is now identified across the DMO and system catalog views as session_id.  "ll see Si Milar changes between previous versions of SQL Server and current versions where all object identifiers is concerned.

You can use the @ @spid () system function to return the session_id of the current session as follows:

SELECT @@SPID

For my test I get session_id = 52.

So, now that we've identified what session_id uniquely identifies the session I ' m using during this demonstration, I'll do A simple query against the Northwind database.

SELECT C.[CompanyName]   FROM [Northwind].dbo.[Customers] C   WHERE C.[City] = ‘Berlin‘   ORDER BY [C].[CompanyName]

At the this point I'll now open up a separate query windows in SQL Server Management Studio. If I now execute the first query above you'll see that this registers as a new session on the SQL Server instance:

SELECT @@SPID

For my test I get session_id = 53

now I can utilize the sys.dm_exec_ Connections dynamic Management View, in conjunction with Thesys.dm_exec_sql_text dynamic Management Function To return the last query statement executed against the SQL Server instance on a selected session.  in all truth, you Can return the last query executed on all sessions, but for the sake of this discussion we ' re limiting the results based Upon the session_id (*) we ' ve identified above.  I ' ll present the query, then we can examine in detail what it provi Des for us.

SELECT DEST.TEXT   FROM sys.[dm_exec_connections] SDEC   CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST  WHERE SDEC.[most_recent_session_id] = 52

The output for this query shows the statement, is run for session_id 52.

So what just happened? Simply-put, we returned the results from the sys.dm_exec_connections DMV, limiting the results by the session_id (*) we  Dentified above. We, submitted the value contained in the Most_recent_sql_handle column of this DMV to the Sys.dm_exec_sql_text Dynamic Mans  Agement Function. That function then returned as text, the value of the sql_handle we passed to it.

so What is a sql_handle?  Think of a Sql_handle as a unique identifier for a query this is unique across the entire SQL Server instance.  Just as a Sessio N_ID uniquely identifies a session, so does a sql_handle identify a query.  the actual value of the sql_handle column is very cryptic.  the value for the Most_recent_sql_handle in this example is shown below:

SELECT SDEC.[most_recent_sql_handle], DEST.[text]   FROM sys.[dm_exec_connections] SDEC   CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST  WHERE SDEC.[most_recent_session_id] = 52

Here we can see the value of the sql_handle and the text translation.

The handle itself does not really does much for us without the function call that rationalizes it into the original query Te  Xt. As can see though, this very simple query does provide us with yet another option for returning information on what US ERS is (or has been) doing on the SQL Server instances we support.

Next Steps

• The Dynamic Management Objects has so much to offer the DBA. Check out the other tips on DMOs from mssqltips.com.
• Read more tips by the author here.
• Still interested in information on sysprocesses, whether as a system table (Pre-sql 2005) or system view? Here is some tips that meet your needs.