SQL Server Management Studio (SSMS) is the one stop shop for almost UI-accessible features of the SQL Server, and an indispensable tool for administrators, deployment professionals and developers alike.
Anybody who has ever worked with Microsoft SQL Server ever since SQL Server 2005 came out would have used SSMS extensively. But, can you tell how many sessions to the SQL Server does a typical (default) SSMS session establish?
As they always say, there’s always a tradeoff. SSMS is a lot more than a query editor, and if you have followed a couple of my recent blog posts, you will find that I talk about the Object Explorer and Object Explorer details window within the SSMS (refer the starting point of the blog series here). Additional features means that we do have some tradeoff, which comes in the form of additional SQL Server connections.
About the DMV
Today, we will be using the Dynamic Management View (DMV) – sys.dm_exec_sessions, which is a very powerful DMV which returns one row per authenticated session on SQL Server. This DMV is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. This view is often used to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.
Once you start using DMVs, you will find that quite often this becomes the starting point of all your monitoring queries.
A point to be noted
A session_id in the results of the DMV – sys.dm_exec_sessions is equivalent to a SPID in the prior versions of SQL Server. Starting SQL Server 2005, the global function @@SPID returns the session_id of the caller.
In earlier versions of SQL Server, the system SPIDs (such as checkpoint, lazywriter, ghost record cleanup, etc) always had a SPID value < 50. Starting SQL 2005, however, this is no longer the case, and a system process may have any value as it’s SPID, as can a user session.
The distinction between a system process and a user process can be made via the “is_user_process” column of the DMV – sys.dm_exec_sessions. Also, since connections are always user processes, this can also be determined via sys.dm_exec_connections.
- Open a New SQL Server Management studio instance. Do not connect to any SQL Server
- Launch the Windows Task Manager and get the Process ID for the SSMS.EXE (If not available by default, go to View -> Select Columns -> Process ID)
- Now, open a new SQL Server Management Studio instance and connect to a SQL Server (in my case, it was VPCW2K3-SQL2K8)
- Run the following query against the SQL Server. Notice that as of now, no results are returned
–In a separate SSMS query window, run the following query with the PID
–fetched from Step 02
FROM sys.dm_exec_sessions sdes
WHERE sdes.host_process_id = 612
- Now, in the test SSMS window (opened in Step# 1), create a new Database engine query by going to File –> New –> Database Engine Query. This time, connect to the SQL Server referenced in Step 03
- Rerun the query in Step #4. Notice that we now get one record in the result
- Now, view the Object Explorer by going to View->Object Explorer. Connect to the same SQL Server as connection #5
- Rerun the query in Step #4. Notice that we now get two records in the result
- Finally, SQL Now, view the Object Explorer Details by going to View->Object Explorer Details
- Rerun the query in Step #4. Notice that we now get three records in the result
The Moral of the Story
Almost every feature of the SQL Server Management Studio contributes to an additional session to the SQL Server targeted. Avoid the urge to use all features at the same time on a production
server, or during troubleshooting – not only will it confuse you, but also might slow down the SQL Server (if the server is under pressure of some sort).
Finally, if the SQL Server has a database which is in single-user mode, it will be available to only one of the multiple sessions that SSMS starts. The trouble is that which connection will come first is unknown. Hence, it is always a good practice to have nothing but the query editor open when working with either single-user databases or single query modes like the DAC (Dedicated Admin connection).
So, configure your environment wisely, and the power of SSMS will be with you.
More Information on the DMVs
If you need more information on DMVs, please refer the following E-book, which is available for free download from http://www.red-gate.com/products/dba/sql-monitor/entrypage/dmv:
Name: SQL Server DMV Starter Pack
Authors: Glenn Berry, Louis Davidson and Tim Ford
Publisher: Red-Gate Software
Be courteous. Drive responsibly.
Nice article Nakul
Hey, thanks a ton, Hardik! Good to see you here.
Nakul, I think this is an INCORRECT statement.
“In earlier versions of SQL Server, the system SPIDs (such as checkpoint, lazywriter, ghost record cleanup, etc) always had a SPID value < 50. Starting SQL 2005, however, this is no longer the case, and a system process may have any value as it's SPID, as can a user session."
Till date (with the release of SQL 2012) systems spids are 1 though 50. spid# 51 or above are all user processes.
SQL Server reserves session ID values from 1 through 50 for internal use, and session ID values 51 or higher represent user sessions.
Internal use, yes. But to say that system processes will never have a session_id > 50 is pushing it. System features can and will have session ids greater than 50.
Please refer Adam Machanic’s ([Blog]) blog post: [http://sqlblog.com/blogs/adam_machanic/archive/2010/06/23/smashing-a-dmv-myth-session-id-50-user-process.aspx] for more details.
Ok, that blog is based on some assumption (e.g. threads for large soft numa, SB, DBM threads etc…which I am not saying are not valid) that more than 50 system spids may be spawned at a give time…but where is proof? So far, I haven’t see more than 50 system spids being spawned and used at a time…however I don’t deny a possibility of it…and if so, the MSDN documentation needs to be changed which clearly states “session ID values 51 or higher represent user sessions”.
If you or someone happens to have a proof, file a doc bug for change in above msdn link.