Finding Serializable transactions

Situation: The main app in my company was creating Serializable transactions, and thus creating Range Locks in production DB.

So, we had to modify the app to change the way transactions were being generated, since we found that the framework was using TransactionScope and, as you may know (or not), the default value for new connections is Serializable. So, the app was modified, and after that, my boss came over to my desk and told me "We need to know if there are some Serializable transactions in XXX environment. Can you check that?" 

Challenge accepted!

Since I had no direct access to the server, and so I cannot left a SQL Profiler running, I had to think in a workaround. So, for that, my idea was to use SQL Server Traces, specifically using the ‘Audit Login’ event (I have to thank Guillaume Columeau for that, an SQL Server expert in Paris). In the TextData column of the trace, you can check the connection isolation level. Also, all connection properties are displayed in that column.

First of all, I had to try the idea in my local environment. So, after creating the trace (I’m not going to get into detailed info about the tools you can use to create them, since there a lot of places in Internet for that. Just Google it ;-) I run the app and did some random flows, which used the DB, with the previous version of the app, a version which I know for sure it was creating serializable transaction. Anyway, this is what I found:

select
      TextData,
      HostName,
      ApplicationName
from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.INTEGRATION\MSSQL\LOG\INTEGRATION$DBM_Logins.trc', default)

(I truncated the TextData field, since it's a little bit long) 

So, indeed, as I was expecting, the app were generating serializable trxs. So I had to identify the SP that was being executed inside it. In the traces, we also can retrieve the execution start time (in the StartTime column)

The SQL instance has also a trace running for the queries, so all I had to do as to check which SPs were executed in that timeframe:

select
      ObjectName
from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.INTEGRATION\MSSQL\LOG\INTEGRATION$DBM_Queries_And_Locks.trc', default)
where StartTime >= '2014-02-17T16:20:21.390'
and EndTime <= '2014-02-17T16:30:44.053'

Searching for that SP inside the app, I could confirm that it was using TransactionScope, with default values, so SERIALIZABLE was being used. Remember that this was a previous version, before the change I mentioned at the beggining of this blog entry, so it was the expected result. So, I was done for the POC, and ready to move to the real environment.

End of story: I replicated all of this in the environment (with help from the DBA, the same guy I mentioned before) my boss asked me to check, and, happily, there was no serializable transaction being generated. so the app was correctly modified.

I’m attaching the code for creating (and stopping) the trace. LoginTrace.zip