The function uses Open Late / Close Early just like the documentation pointed out. Now imagine you have dozens or 100s of these functions in the application. The problem is that seldom does logical business activity call a single function activity.
In this example the application drives the connection pool 3 times, resulting in 3, sp_reset_connection operations. This is the worst case scenario with a 1:1 ratio of commands to sp_reset_connection invocations.
Don’t fight over the grapes
You may have wondered why this was in the title. It is because I was recently sitting in an airport and two little girls gave me an analogy for sp_reset_connection.
They looked to be about 3 or 4 years old and were sharing a bag of grapes. They started out very polite, each taking just one grape at a time. However, they kept waiting on each other and as time went on they got a bit more combative until the mother finally told them not to fight over the grapes. Instead of taking one at a time take a handful. Then they would not be constrained all the time waiting to get access to the bag of grapes.
If you will, she optimized their activity.
Bob Dorr - Principal SQL Server Escalation Engineer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.