Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

The multiple connections of SSMS

Jan 17 2011 12:00AM by Nakul Vachhrajani   

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.

Tags: Development, Administration, T-SQL, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, DBA, SSMS, Tools and Utilities,


Nakul Vachhrajani
4 · 36% · 11645
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

5  Comments  

  • Nice article Nakul

    commented on Feb 1 2011 6:30AM
    Hardik Doshi
    20 · 9% · 2864
  • Hey, thanks a ton, Hardik! Good to see you here.

    commented on Feb 1 2011 6:55AM
    Nakul Vachhrajani
    4 · 36% · 11645
  • 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.

    commented on Mar 29 2012 12:55PM
    Prashant
    2855 · 0% · 3
  • 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.

    commented on Mar 31 2012 5:48AM
    Nakul Vachhrajani
    4 · 36% · 11645
  • 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.

    commented on Mar 31 2012 8:08AM
    Prashant
    2855 · 0% · 3

Your Comment


Sign Up or Login to post a comment.

"The multiple connections of SSMS" rated 5 out of 5 by 3 readers
The multiple connections of SSMS , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]