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.
RSS
I use SQL all the time! I always debug as much as possible for the devs.
You spelled "DB" as "BD" at the end of the 2nd paragraph. Also forgot to mention the name of the tool for tracing is called "SQL Profiler". And you also used a semicolon more than once in your post (only max of one is allowed in a post per MLA).
I like how your name for programmers "Prog" is one character off from my real name.
I also like Laura's comment about how she debugs as much as possible for the devs. Hire her on our team!
Although I don't think it's necessary to do so but is much appreciated and does show a tester going over and beyond the call of duty, but I do see how doing so might be useful in certain instances to look at SQL (or any code for that matter) as you can find flaws that may lead you to think of a test that could cause the feature to fail otherwise.
ie. if all your tests passed but then you look at the SQL and you notice an INNER JOIN to a table to lookup data to display in the grid, however said lookup data may not always be required/provided relative to the parent table being joined against.
So the INNER JOIN would fail when the non required child record data isn't set. The fix would be to do a LEFT OUTER JOIN to allow nulls for the missing child record data, the INNER JOIN would just fail to return the parent record altogether if the child record doesn't exist. This is a common SQL join bug.
Thanks for the corrections, idontlikegluefoo. I applied most of them.
Yes, I should hire Laura. In fact, I have an open position on my BI team. If you're interested let me know, Laura.
I started using SQL Profiler today because if this post. Thanks Eric!
Great stuff Eric, I'm going to have to poke around with SQL Profiler to see if that can help narrow down user data issues on some of my sites!