Difference among Spid, Kpid, Ecid, dbid, and uid

It is easy to find these ids in SQL server and what is their importance and role. I was asked a question based on these different ids in an interview so I collected more info on the same and now publishing for our DBA friends.

How to find Spid, Kpid, Ecid, dbid, and uid in SQL Server.

Go to SSMS and open a new session

Use Master

Select * from sys.sysprocesses

Result would be like this:

 sid ecid

Sys.sysprocesses contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes.

Here,

spid:  SQL Server session ID.

kpid: Windows thread ID.

dbid: ID of the database currently being used by the process.

Uid: ID of the user that executed the command. Overflows or returns NULL if the number of users and roles exceeds 32,767.

ecid: Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.

If you feel that this information is useful please leave a comment or like which will encourage me to write other stuff in future.

Many Thanks!

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration and tagged , , , , , . Bookmark the permalink.

3 Responses to Difference among Spid, Kpid, Ecid, dbid, and uid

  1. Shakti Singh says:

    It is use full for me…

    Like

  2. dbuskirk says:

    Mirza:
    Just wanted to mention that uid sometimes is the user id and sometimes not. Prior to SQL Server 2005 schemas did not exist; tables and procedures belonged to users. System tables like syscacheobjects tracked the user who executed a query in a column called uid. In 2005 and later, tables and procedures belong to schemas. Compatibility view sys.syscacheobjects still has a column called uid, but it tracks schema, not user id. It is interesting to note that if a query uses a two-part name, i.e. dbo.TableName, uid is set to -2, meaning that the default schema of the user is not relevant to the execution of the query.

    Best regards,
    Dan B.

    Liked by 1 person

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s