The absolute simplest thing you can do to save considerable time debugging your database-driven applications is start up SQL Profiler.

I'll admit. I didn't even realize this existed until several years ago at CFUNITED when Charlie Arehart gave a presentation that somehow moved on to showing us the profiler. I can't pick out which one of his many great presentations it was. but what a fortunate turn of events to have been in that room at the time!

I've found this to be most useful in our Ajax applications - situations where we would traditionally use a brute force CFDUMP to see what's going in to the db aren't always possible. Hacking up your code to write the sql to a file or email is nasty and time consuming. Why not just watch the wire instead? Here's how that's done:

Let's start from square one. Start->All Programs->Microsoft SQL Server -> Profiler.

Select "New Trace" from the dropdown menu or the leftmost icon on the toolbar. Connect to your desired database server. In my case. I'm using localhost and windows credentials. YMMV.

The next screen is the most important one: what do you want to see in the log? The names are terrible and don't say enough. Let's over-simplify for a moment and pick the SQLProfilerTSQL_SPs template. To be honest. this is the only one I have used so far.

Don't do anything else - just hit "Run". You'll see some startup messages and what not. Ignore them.

Run a quick template on your site. Not too much - you'll quickly overwhelm yourself (Ctrl-Shift-Del to clear the trace window). You'll see stuff like this:

Here's how you piece that together: the "exec sp_execute..." line is the params being passed in to the SQL statement defined on the following line (UPDATE. SELECT. whatever). At that point it becomes trivially easy to stitch it back together and see what's going on.

Of course. there's FAR more to profiler than this dinky little example. but in terms of getting started without messing with your code it can't be beat. Use it to check security. run performance tests. log a problematic long-running query or simply get around having to touch your code for debugging. If you find a particularly good resource for learning more about profiler. post it here in comments.

*sigh*

Another performance bottleneck to investigate...

Thanks. Oracle. Thanks a whole lot.

We were doing a little bit of experimenting in the labs today with large SQL datasets and complex nested queries. We're talking about 1.5 million rows of data in our primary source table. a few blocks of subselects in the query...

Running it straight up as a CFQUERY using CFQUERYPARAM we were seeing about 7 second response times. Eh. not good enough.

Took the code. dropped it in a stored procedure...same result.

So we told Query Analyzer to show us the Execution Plan. Saw about 80% of the cost was a series of index scans - typical. not a big deal. but I figured we could tweak the indexes a bit. We added a couple extra ones manually but that actually increased response times from 7 to about 10 seconds. Oops!

It was at this moment we figured we'd see what happened when we let Microsoft drive. There's an option on the toolbar for the Index Tuning Wizard. We set it to run using the stored proc as the workload to be tuned. A few seconds later it reported it was done with an estimated 99% improvement. Yeah. right!

Imagine our surprise when running that proc was returning data in less than 1 second!

More experimenting will be done with other queries. The hope is that we didn't kill performance for the rest of the (mostly unwritten) application just to meet this one need. Either way. we're pleasantly surprised with how well this worked out.

Just goes to show you that we as developers have a LOT to benefit by learning more about proper database tuning. I know we've barely scratched the surface. There's a reason a good DBA makes a lot of money!