像.net中的session一样，如果能知道了数据库中的sessionID,那所有的操作都能知道了，因为有了这个唯一的身份识别的标识。

可以做的事情有很多，如：当前哪个用户在做什么操作，在执行什么sql, 又如一个比较大的逻辑中要分别执行很多存储过程，

在执行这些存储过程的过程当中，你想知道当前执行的进度，SQLServer正在执行哪个段sql语句，那么通过sessionID是很容易

就得到这些信息的。

SQL Server 得到SPID,唯一的sessionID：

SELECT @@SPID

以前我一直不知道，最近又装了SQLServer2014,发现每开一个Query 界面就有一个ID出来。我就特别想知道怎么取sessionID.

下面的存储过程是用来查看哪些sessionID正在执行什么操作。

create PROC [dbo].[dba_WhatSQLIsExecuting]
AS

BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


-- What SQL Statements Are 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 > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
--and DB_NAME(sp.dbid)='RangeCheckTool'
ORDER BY 1, 2
END

还可以参考下面的文章：

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 executed for a given session. He offered up a couple options to return the last query statement executed, settling upon querying the sys.sysprocesses system compatibility view, but another way that this can be done is through the Dynamic Management Views and Functions. The process for doing so is quite straight-forward and works in all versions of Microsoft SQL Server since DMOs (dynamic management 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 synonymous with a user process. Previous to SQL 2005 sessions were referred to - and identified solely - as SPIDs (short for session id). A SPID uniquely identifies a session and a SPID is unique across the SQL Server instance. In an attempt to conform SQL Server object identifiers to be more user-friendly and to standardize a naming convention across all system objects, sessions are now identified across the DMO and system catalog views as session_id. You'll see similar changes between previous versions of SQL Server and current versions where all object identifiers are concerned.

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

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.

At this point I'll now open up a separate query window 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: