April 27, 2018 at 9:38 pm
I really thought I would be able to find this topic
We have been experiencing some weird Tempdb growth that I haven't been able to figure out I had been trying to monitor what what happening in the tempdb dirong growth events and the data just seemed weird. So today using 3rd party software it snapshot the tempdb transactions and I noticed that the spids remained the same and the values were accumulating. I then realized that there was only 1 session per web server and the sessions and spid were over 6 hours old. I was under the impression we were using connection pooling, so I asked a dev. He said "optimized to use the same Session since it never changes its connection information.
This is a singleton by design"
So I am thinking to myself if the same session and spid is being used how does the tempdb manage the transactions? I have been googling and not really finding any information. I know, I am not finding helpful data because all of the data is cumulative.
Has anyone else experienced this? I always thought best practices was connection pooling and closing the connection as soon as possible for security.
Sql 2014
AG Environment
Compatibility level is 2008r2
Calling app is .net
it is using Windows Auth
App pools, but appears to be circumventing the connection pooling.
April 30, 2018 at 8:38 am
You are right, using the connection pool is the better way to go, especially if you ever have two people hit the website at the same time.
There are a couple of things you could do to have the tempdb grow in this situation. Unlikely is you could be creating a lot of temp tables, and not dropping them. Coming up with a system of dynamically creating a lot of temp tables without reusing the names of them would be a bit difficult.
You could also have a database in an odd snapshot isolation mode. I have seen this with a third party application a few years ago, but I am hazy on the details, now. Can you narrow down whether the space in tempdb is being used by the version store. Check this article to rule that in or out:
https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/
April 30, 2018 at 10:38 am
swoozie - Friday, April 27, 2018 9:38 PMI really thought I would be able to find this topic
We have been experiencing some weird Tempdb growth that I haven't been able to figure out I had been trying to monitor what what happening in the tempdb dirong growth events and the data just seemed weird. So today using 3rd party software it snapshot the tempdb transactions and I noticed that the spids remained the same and the values were accumulating. I then realized that there was only 1 session per web server and the sessions and spid were over 6 hours old. I was under the impression we were using connection pooling, so I asked a dev. He said "optimized to use the same Session since it never changes its connection information.
This is a singleton by design"
So I am thinking to myself if the same session and spid is being used how does the tempdb manage the transactions? I have been googling and not really finding any information. I know, I am not finding helpful data because all of the data is cumulative.
Has anyone else experienced this? I always thought best practices was connection pooling and closing the connection as soon as possible for security.
Sql 2014
AG Environment
Compatibility level is 2008r2
Calling app is .net
it is using Windows Auth
App pools, but appears to be circumventing the connection pooling.
Singleton Connection method applies to Connections, not sessions. They are two different things. You are correct about connection pooling and a singleton database connection is not considered a good idea. You can find a lot of discussions around the problems when using that pattern for connections. Do a search on: singleton database connection
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply