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)
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
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 result
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 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.