July 28, 2010 at 6:25 pm
1. Please can someone explain the link between Connections and Sessions.
2. What is a session, and how is it different to a connection?
I have been using the following DMV's:
sys.dm_exec_sessions
sys.dm_exec_connections
3. For example, how many sessions can a connection have and vice versa.
4. Also is it possible to see which database is being connected to, via the above DMV's?
Thanks for any info.
July 29, 2010 at 7:10 am
I could be wrong about this, but this is how I understand it.
Sessions "contain" connections.
A query can connect with multiple threads to the server to assist with performance and processing. One SELECT statement theoretically could have five or more connections going at a time, or just one single connection.
Connections can also be pooled together so that when one query / person is done with a connection, that connection can be picked up by another query / person.
A session remains open until all connections used by that session is closed. Sessions can be closed from the server end, but developers and DBAs are supposed to be closing their connections and servers when the query is finished running (from the remote end).
Any "memory" that is held aside for a session will "disappear" (this is a poor word choice, but I'm not quite awake yet) when the session is closed, but not necessarily when a connection is closed by the session remains open. The best example of this is the single # Temp tables. You open a window in SSMS, do a SELECT ... INTO #Temp... and the temp table will remain in the memory of that session until you exit that query window or disconnect that session from the server (or the server's services go down). Even if you do no more than just let it sit there all day, #Temp will sit there in memory, waiting for the next query.
Does that help?
July 29, 2010 at 7:20 am
ggjjbb1983 (7/28/2010)
1. 3. For example, how many sessions can a connection have and vice versa.
Depends on how your server is set up, how many CPUs you have, how heavy the load is on the server at the time a session is in play, etc. There is no one true answer to this question. Check your Server Properties, though, and go to Connections. Use that information (and the information on your CPUs) to look up in Books Online what Microsoft's official answer to that question is.
ggjjbb1983 (7/28/2010)
4. Also is it possible to see which database is being connected to, via the above DMV's?
Honestly, I've never played with those DMVs. But after doing an sp_helptext and looking at system views in one of my local databases, both DMVs use OpenRowSet and they inhabit the local user database.
When I run the DMVs against two different user databases, however, I get back the same data. So even though the views are local to the different databases, they seem to be running like a Master db view or proc. I'm getting everything, regardless of database.
Use sp_helptext on the views, put quotes around it first -- like 'schema.viewname', and you'll see what I mean. Follow that code back and you might be able to find something that contains a database id for you to hook up to sys.databases and get the name value back.
July 29, 2010 at 12:00 pm
Thanks for the reply. Please can you answer the following if possible:
1. Start SQL Server 2005
2. Connect to Server (Instance)
3. Open 3 'New Query' windows
4. Run the sys.dm_exec_sessions, and sys.dm_exec_connections
5. I have 3 connections and 3 sessions.
Why do I have 3 connections? I connected to the server once when I started SQL Server. How does this give me 3 connections.
I understand that I have 3 sessions as I have opened 3 query windows and ran 3 new queries. But where do the 3 connections come from??
Thanks..
July 30, 2010 at 5:57 am
Each window is a separate session. And since you're not doing much of anything else, there's only 1 connection per session.
EDIT: Each session has a minimum of 1 connection per session. You can't have a session with zero connections unless the connection has been killed elsewhere (such as when the services go down and disconnect the connection, or when someone uses the KILL command on your SPID). In which case, the window is still open, but the session is pretty much defunct.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply