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