Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time?

If it's free, that would be great.

| improve this question | | | | |

Use a SQL Trace, and if you are looking for a GUI to go along with it you can use SQL Profiler. It ships with all versions of SQL Server except SQL Server Express. SQL Trace is utilize directly through T-SQL and is available even with SQL Server Express, though a pretty good working knowledge of SQL Trace is desirable as it can be a little daunting to set up without the GUI.

What you would do is select the events that pertain to SQL statements/batches/stored procedures executing. You will be able to capture the actual text statement(s).

If you don't want to wrestle with selecting out of the available (and many) events, you can use a built-in template. It sounds like you'd probably want to use the T-SQL template, but look through it before just blindly running the trace.

BOL Reference on SQL Profiler

At the botton of that linked reference, there are a handful of "tasks" (tutorials) to get you started in the right direction.

EDIT: Aaron brings up a good point (see comments). Utilizing SQL Server Profiler is typically used for a quick glimpse or relatively short trace to do some snap troubleshooting. There are definitely performance impacts, and I have seen busy servers brought to their knees because of it. If you are looking for a longer, less performance impact trace I'd look to use T-SQL to generate the server-side traces. Another great alternative is Extended Events.

BOL Reference on Extended Events

| improve this answer | | | | |
  • 4
    +1 but I would also mention that scripting a server-side trace, and sending the output to a file (or a table if you must), and reviewing the events after the fact, is going to have a much lower impact on a production server than opening up profiler and trying to watch the activity in real time. As soon as you start pumping that output to Profiler's mickey mouse UI you're asking for trouble. I know it's what you meant up top, just wanted to be more explicit about why the UI is "less good." – Aaron Bertrand Apr 24 '12 at 17:57
  • 1
    @AaronBertrand Great point, and I have edited my post to explicitly state that. Profiler can make a bad day even worse...very quickly. :) – Thomas Stringer Apr 24 '12 at 18:02
  • It depends on the interval set for refresh if using to see every sql statement being run. I beleive if set to 10 secs or under then you will impact performance. Otherwise SQL profiler has built in trace templates and is used to find performance issues. UI does not make it a performance problem either same issue can occur running powershell script. Valid point though to watch out for. – SoftwareCarpenter Apr 25 '12 at 15:47

Use "SQL Profiler". It comes free with SQL Server 2008.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.