Do Idle Sessions use Resources

Question

I am a data analyst/report developer for a very large company. I use SSMS daily. I only have access to certain data; no access to the server, explain plan, or anything like that. The first thing I do each day is to open a copy of SSMS in which I create several global temp tables. Those temp tables provide the data necessary for several tabs in an Excel report. I also leave that copy of SSMS open throughout the day so the data in the temp tables is readily available for ad hoc reporting. This is not and has never been an issue.

Throughout the day I usually have at least one more copy of SSMS open with at least one query window, and typically three or four windows, open. I use that copy of SSMS to perform research and analysis for report development. Recently, I have been contacted by one of the DBAs telling me that I have a long running session and asking me to close it.

The situation is this: I open a session and run a query, which may finish in under a minute or take fifteen minutes or longer, but rarely do I let anything run over 30 minutes. I leave the session open - maybe I copy the results to Excel to analyze the data or I just move on to another window/session to do some more research. In the afternoon, I rerun the query in the session that I've had open since morning. While the query is running, the DBA contacts me and tells me the session has been open too long and is holding up resources.

Nothing is ever said about the ten sessions that remain open in my first open copy of SSMS. The DBA only contacts me if I am running a query in a session that has been open a long time (again - not a long running query but a query running in a long open session).

Question: Does that session pose any problem for the server during the time it is idle? Does the query that is running later in the day in that session use any more resources than the first time it ran?

Thank you very much,
Gregory

Answer 1

An open query window in SSMS does take up some resources, particularly there is an amount of memory allocated for the connection. However, for a single or even ten connections, this is nothing the DBA should worry about. When this can be a problem is if an application leaves thousands of connections open.

You say that you fill up temp tables in your first session. These temp tables take up space in tempdb, and if they are several gigabytes in size, the DBA may be concerned. Whether he/she should be concerned, I don't want to make a judgement of with the little I know at this point.

What absolutely can be a problem is if you do:

BEGIN TRANSACTION
-- Do something

and never commit that transaction. You would be holding locks that could block other users. But there can be problem even if you would only operate on your private tables, since that open transaction log prevents the transaction log from being truncated.

There is actually an option in SSMS that can help you to make friends with the DBA. Tools->Options->Query Execution->SQL Server->Advanced->Disconnect after the query executes. However, if you disconnect, you would lose those temp tables, so it does not sound like it would suit you.

As SimpleSamples say, you need to work with the DBA. Maybe there is a real problem that the DBA has not been able to communicate clearly, or which did not register with you, because you may not have the technical understanding to SQL Server to grasp it. Or maybe the DBA is just over-zealous. All this is hard to tell from a distance.

Answer 2

An important question is, what is that one session doing? What do you use it for? You say you use SSMS for:

• data necessary for several tabs in an Excel report
• ad hoc reporting
• research and analysis for report development

You say nothing is said about the first SSMS session. Oh, okay; now I see. The first SSMS is for the temporary tables. The one that the DBA complains about is for the production data, correct? It makes sense to me that the problem is the connection to production data, not SSMS. Must you keep an open connection to the production data? Can you disconnect when you do not need it and connect only when you need to?

Perhaps you can find some other software to do the research and analysis for report development. SSMS is not really designed to be used as much as you do for the purposes you are using it for. Have you asked the DBAs for a solution? Write a description of your requirements. Explain what you need to do and why. Make a strong and clear business case for what you need to do. Coordinate with management. It is likely the DBAs want to support your organization as much as you do.

Your situation reminds me of a situation I was in 30 years ago. Totally different software but the technical people did not like me keeping my session open for long periods of time. I will not bore everyone with the details of that but I have been in your situation. I really suggest you work with the DBAs as much as possible.