When we find a bug with repro steps we typically leave the deep investigation to our Progs and move on to the next test. But sometimes it’s fun to squeeze in a little extra investigation if time permits.
My service calls were not returning the expected results so my Prog showed me a cool little trick; how to capture the SQL being passed into my DB for specific service calls. This technique is otherwise known as performing a SQL trace. We ran a SQL trace and with a bit of filtering and some timing, we easily captured the SQL statements being triggered via the called service. We extracted the SQL from the trace and used it to perform direct DB calls. Said isolation helped us find the root problem; a bad join.
Ask your Progs or DBAs which tools they use to perform SQL Traces. We used SQL Profiler and SQL Server 2008 R2. Make sure they show you how to filter down the trace (e.g., only show TSQL, to a certain DB, sent from a specific trigger like a user or service) so you don’t have to sift through all the other transactions.