SQL profiler is pretty awesome. What else is cool is taking the resulting trace file and feeding it through the optimizer to see if you need to tweak your indexes. A few times I have found things to increase SQL performance almost 30% or more!

SQL profiler is one of my favorite tools for debugging/optimizing applications.

Now that you have discovered it's usefulness, you will also soon discover one of the down-sides of using cfqueryparam !

Which is? I was testing w/ a .net app.

Do you mean that the SQL statements contain weird crap for the bind params? If so - I saw them, and I saw the values later in the profile line, so it was still readable.

Being a full-time MS SQL DBA, Profiler is one of those essential tools for my job. Not just for debugging, but for optimizing stored procedures and identifying resource hogs in our production environment.

Don't feel left out though, Ray. Nobody at our company was even familiar with it before I came on board. It's often overlooked by those who don't have an immediate and regular need for it's help.

Quest seems to have Spotlight on MySQL ( http://www.quest.com/spotli... )

I've used Spotlight with Oracle and have to say that it's impressive piece of software. Not only you get to see what is happening within database but you will also get suggestions what you could change to make it work better.

Definitely a tool I would suggest if you can afford it - but I haven't tried MySQL version so I can't comment about it.

Tero

The best part is that you can save the trace into a table. That way you can delete all the garbage data that you don't need. You can also create your own template that just looks for certain usernames etc etc

Anyone got a tip for setting the app name in the cfadmin connection string?

Have tried both app=foo; and Application Name=foo;

With SQL Server 2000 it seems to be "ProgramName" according to JDBC documentation.

Ray, the problem with CFQUERYPARAM and trying to debug with SQL Profiler is the fact that the query comes out as SP(all your parameters). There is no SQL in the profiler and you have to do a lot of digging to try and figure out where the original SQL was created and then try and match it up to the SP. It is very difficult. I believe the benefits of CFQUERYPARAM far outweigh this problem, it is something I fight with my DBA and managers on all the time.

Interesting. When I was profiling my .net app, I saw SQL statements that looked just like how CF shows SQL in the debugger that includes cfqueryparams, ie it used a ?1 or some such, and at the end there was a list of values.

Since it looked just like CF's print of queries with queryparam, I just assumed.

I'm going to play with this today as I wanted to do a Captivate movie of it.

Yes Ray you're the last one to figure this out. ;-)

Just wait till he finds Query Analyzer :)

Hey now - at least THAT I know about. ;)

Ray,

Would you care to post a primer on SQL Profiler?
For people who are not too savvy with the administration side of SQL Server, (such as myself), I'd love to be able to leverage this tool, and monitor which of my db calls may be taking longer to execute or creating bottlenecks.

Maybe like an idiot's guide to the SQL Profiler.

Thanks,
Ali

I plan on doing a Captivate demo of it. I don't consider myself knowledgeable enough to make a guide. All I learned was how to monitor the SQL. But I can show that for sure.

Cool, can't wait to check it out.
Thanks

you are actually the second to last person on earth to know that. I think I am the last. You beat me to it.