Use Profiler to do detective work on your queries

Have you ever considered using SQL Server Profiler to troubleshoot problems you're having or to find answers to your questions? You should. As a SQL Server Most Valuable Professional (MVP), I answer many questions on the public Microsoft SQL Server newsgroups (msnews.microsoft.com). I recently answered a question similar to this one:

"The Query Analyzer object browser lets you list the parameters for a given stored procedure. Where does this list live, and can I query this table directly? I need to build a list of all my procedures and their parameters, and I'd like to know how Query Analyzer gets its information."

I easily could have just answered this question. However, I'm a big fan of teaching someone how to fish instead of giving him or her a plate of microwavable fish sticks. I suggested that the reader use Profiler to discover the answer.

Query Analyzer, Enterprise Manager, and other SQL Server tools perform an amazing number of tasks that you might want to replicate from time to time. You could write a question, post it on a newsgroup, then wait hours or days for a reply. Or you could take a few minutes to find the answer on your own by using Profiler. (See SQL Server Books Online—BOL—for more information about Profiler if you're not familiar with this handy and powerful tool.)

Enterprise Manager and Query Analyzer both retrieve most of the information they display by running plain, old-fashioned stored procedures or queries against the server. Profiler lets us see what SQL statements are running on the server. Not sure how Query Analyzer performs a task? The answer is simple to find. Start Profiler, and perform the task in question from Query Analyzer or Enterprise Manager. You'll usually be able to see exactly what the tool did to generate the answer you're interested in.

If you're really stuck, you can always post a question on a newsgroup or ask a buddy. But I encourage you to first tackle the problem on your own. You'll learn much more and remember the information longer. In addition, you'll probably stumble across answers to questions that you didn't think to ask.

Profiler isn't a panacea to all your SQL Server research questions. But I've learned a tremendous amount about the way SQL Server works by using Profiler to watch what the tools do. You can, too.