5

I have a .NET 4.0 Winform and a .NET 4.0 Windows Service which both connect to a SQL 2005/2008 database through LINQ to SQL. It runs nice and fast on our test environment with a perfect clone of production data, but on the Production environment, it runs really slowly and has low CPU usage and bandwidth usage. I also noticed hundreds of SQL timeouts a day, even for the smallest queries on a well-indexed database. So I fired up the Profiler...

I found that sp_reset_connection accounted for one third of total SQL CPU duration and 90% of all total SQL calls during a 10-minute capture under load.

  • I have tried disabling & enabling connection pooling and fiddling with the number of allowed connections and connection timeouts in the connection string. None have had any effect.
  • I have been replacing my LINQ queries with ADO.NET queries as I come across them. These old ADO.NET queries never time out. Only the LINQ ones.
  • I have noticed other major performance issues on that server, but I'm not sure how to approach the subject with the customer's sysadmin.

  • I have Admin access on the Application server the service runs on. I have next to no access on the terminal server where they run the Winform, nor to the SQL server.

  • What causes sp_reset_connection to run so often?

  • Is there a way to circumvent these calls without tearing all the LINQ out of my application?
  • Is there a way to reduce the number of calls to this stored procedure?
  • Is there a way to reduce the amount of processor time the SQL server needs for these calls?
  • Will I mess anything else up if I leave pooling disabled and replace that stored proc with, say, an empty one?
tsilb
  • 7,977
  • 13
  • 71
  • 98

2 Answers2

4

Found some other pages about this, one suggested this:

For your connection reset, open your DataContext connection before working with it and close it at the end. 

db.Connection.Open()
... work...
db.Connection.Close()
Pleun
  • 8,856
  • 2
  • 30
  • 50
  • 2
    It would appear data context issues `sp_reset_connection` after each command within the `using` block, unless you open the connection manually (that is something I never actually did because it was counter-intuitive) or have an ambient transaction (which I always had, so it worked for me and I didn't even realize why). Can you give a link to "some other pages"? Would like some documentation on that behaviour. – GSerg Sep 24 '12 at 18:50
1

sp_reset_connection occurs when a sqlconnection returns to the connection pool, now this should not be a problem.

but now the question is why do you get timeouts? is it the sql server that can't cope with the amount of transactions or is it that the connection pool depletes, never used Linq-to-sql but make sure you dispose every thing you can dispose when your done with the objects...

Edit: the connection pool is there for a reason it removing it will likly worsen your performance and removing "sp_reset_connection" will give you weird errors as data will be carried over to the next user of the connection...

to lower the amounts of sp_reset_connection the only way you can do is try to reuse the same connection for as many queries as you can!

Peter
  • 37,042
  • 39
  • 142
  • 198