I was having this problem at a customer site where we were seeing very poor performance in one of our applications that utilized an API that connected to a SQL database. At first glance I thought there was some memory leak in my code. I went through it line by line and made sure that everything was cleaned up in all circumstances. I found a few places for improvement, ran the code again, and we were still experiencing performance issues.
After a bit more research (and fiddling around), I found that if I restarted SQL server many of my problems went away. I started to think there was an issue with some of the maintenance plans on the database or some processor or RAM setting in SQL server. After checking all of the settings multiple times, I found that everything was set correctly.
On a whim, I decided to run
Process Explorer, by Sysinternals, to see if it could tell me more. Well it definitely showed me more. I found that there were handles running wild in the program. There were 3 in particular that kept showing up:
\BaseNamedObjects\{SERVERNAME}MSSQLSERVER_AcceptReady
\BaseNamedObjects\{SERVERNAME}MSSQLSERVER_MUTEX
\BaseNamedObjects\{SERVERNAME}MSSQLSERVER_SERVER_SHARED
This made me focus on SQL server again. I started writing very small test applications and found that the error only occurred when I connected to the database through one particular DSN. I deleted and recreated the DSN and that fixed all of my issues. I wish I had kept the old DSN around, just to see what the exact cause was, but I do have a theory about what happened.
The ODBC DSN entries contain pointers to a driver file to use. It is my contention that the faulty DSN was pointing to an old driver (i.e. SQL SP2) which had a known memory leak in it when connections and disconnections were done very frequently.