CODEWITHSUNDEEP
c#iissqlconnectionsqlperformance
Code_Worm
Code_Worm

Reputation: 4470

SqlConnection.ClearPool() impact on webserver(IIS) performance

I recently monitored my sql database activity I found about 400 processes in activity monitoring.Later I figured that the problem is with my connection string object which would not be cleared physically even though I completely closed and disposed it, so once I suspend my IIS all the processes from activity monitoring would disappear.

after a little searching I found that I can clean all of my connections from application pool so that all the useless processes from SMSS would be killed but

I'm really concerned about it's impact on webserver. It's true that this approach would clear useless tasks from SMSS but for every request a new connection should really be created is it worth it???

considering my application is kind of enterprise app which is supposed to handle to many requests, I'm so afraid of making IIS server down by using this approach.

Do notice that my connection string value is not completely fixed for all the requeests, I made it variable by changing only "Application Name" section of it in every request according to the request parameters for the purpose of getting requestors information in sql activity monitoring and sql profiler.

is it worth to do so considering my business scope or it's better I fix the connection string value in other word is performance lag on this approach is so severe that I have to change my logging strategy or it's just a little slower???

Upvotes: 1

Views: 646

Answers (1)

usr
usr

Reputation: 171178

Do notice that my connection string value is not completely fixed for all the requeests, I made it variable by changing only "Application Name" section of it in every request according to the request parameters for the purpose of getting requestors information in sql activity monitoring and sql profiler.

This is really bad because it kills pooling. You might as well disable pooling but that comes with a heavy performance penalty (which you are paying right now already).

Don't do that. Obtain monitoring information in a different way.

Besides that, neither SQL Server nor .NET have a problem with 400 connections. That's unusually high but will not cause problems.

If you run multiple instances of the app (e.g. for HA) this will multiply. The limit is 30k. I'm not aware of any reasons why this would cause a slowdown for the app, but it might cause problems for your monitoring tools.

Upvotes: 1

Related Questions