Last post, I talked about Customized Event and setting up trace for web applications which usually use pooled connection. Assuming you already have everything setup, SqlCommand component has been wrapped up to send an customized event with user or session information before an actually procedure call taking place. The trace is also setup to receive both procedure calls and customized events. To simplify the scenario, trace only capture the customized event and RPC Complete with column SPID, TextData, StartTime. To receive the trace data, you can either save them to the disk then read the trace file from the disk or directly stream the trace data.( see my post here for detail). After trace data being read, there will be always a customized event prior to an actual procedure call. When you are asked to get SQL Server response time for an particular application, for instance, give me average respose time of procedure ABC every 30 minutes during the day for that user, you can query the table includes the trace rows to locate all procedure ABC, then based on SPID and identity values to find previous record to get the user information of the procedure call, perform filter, aggregates…It will be fine if your table is small. The trace system generate more than 500MB data every hour, performing such query will be very challenging irrespective of how you design the indexes on the trace table. Now you may think to re-format the trace data to table with columns, EventID, SPID, TextData, StartTime, and UserInfo to simplify the query. But SQL Profiler cannot do it in that way, you will have to write an ETL for that.
SQL Clients
Few Things About Pooled Connections
Pooled Connections are not a new concept of SQL Client. It reuses pool of opened connections for any new T-SQL requests. It provides a logically fresh new connection to application as you physically open a new connection. The fact behind it is that when you close a pooled connection, the connection is merely returned back to the pool without physically closing the tunnel(SQL Server doesn’t know the connection is closed by the client), and, when you open a new connection, SQL Client will open a new connection if there is no available connections in the pool. Otherwise SQL Client will get the connection from the pool. In this case, SQL Server still does not know that the connection is assigned for new purposes. Whenever you run the first command through a connection which assigned from the pool, SQL Client will “inject” execution of sp_reset_connection before the execution of your SQL statement or procedure to reset resources and mimik a brand new connection. A lot of discussions regarding sp_reset_connection in the internet. I will excerpt them at the end of this blog post. Apart from that, you must know few other things about it.
Deadlock in Pooled Connections
When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.