The multiple connections of SSMS


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.

The Demonstration

  1. Open a New SQL Server Management studio instance. Do not connect to any SQL Server
  2. 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)image
  3. Now, open a new SQL Server Management Studio instance and connect to a SQL Server (in my case, it was VPCW2K3-SQL2K8)
  4. 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
    SELECT *
    FROM sys.dm_exec_sessions sdes
    WHERE sdes.host_process_id = 612
    ~~~
  5. 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
  6. Rerun the query in Step #4. Notice that we now get one record in the result image
  7. Now, view the Object Explorer by going to View->Object Explorer. Connect to the same SQL Server as connection #5
  8. Rerun the query in Step #4. Notice that we now get two records in the resultimage
  9. Finally, SQL Now, view the Object Explorer Details by going to View->Object Explorer Details
  10. Rerun the query in Step #4. Notice that we now get three records in the resultimage

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.

Advertisements

5 thoughts on “The multiple connections of SSMS

  1. prkuma

    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.

    Ref: http://msdn.microsoft.com/en-us/library/ms174313.aspx
    SQL Server reserves session ID values from 1 through 50 for internal use, and session ID values 51 or higher represent user sessions.

    Like

    Reply
  2. Nakul Vachhrajani

    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][1]) blog post: [http://sqlblog.com/blogs/adam_machanic/archive/2010/06/23/smashing-a-dmv-myth-session-id-50-user-process.aspx][2] for more details.

    [1]: http://sqlblog.com/blogs/adam_machanic/default.aspx
    [2]: http://sqlblog.com/blogs/adam_machanic/archive/2010/06/23/smashing-a-dmv-myth-session-id-50-user-process.aspx

    Like

    Reply
  3. prkuma

    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.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

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