Real time preview of SQL queries generated by applications

Profiler Trace has been deprecated but you can still use it. Additionally, there are still a few ways to see what's going on:

• sp_WhoIsActive from Adam Machanic ... this is an excellent tool to use if you want to know what's going on right now
• because I don't have sp_WhoIsActive created on all instances right now, I also use a less elegant script that I put together here http://gist.github.com/swasheck/11379471 that uses a combination of DMVs that may be of interest to you (also for right now).

Depending on your SQL Server version, you can use the system_health Extended Event session.

Here's a way to query system_health in SQL Server 2008:

        SELECT 
            td.r.value('@name','sysname') event_name,
            td.r.value('@timestamp','datetime2(7)') event_timestamp, 
            td.r.value('(data[@name="wait_type"]/text)[1]','sysname') wait_type,
            td.r.value('(data[@name="duration"]/value)[1]','bigint') wait_duration,
            td.r.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
            td.r.query('.') event_data
        into #xe
        FROM (
                    SELECT
                    CAST(target_data AS XML) target_data
                    FROM sys.dm_xe_sessions s
                    JOIN sys.dm_xe_session_targets t
                        ON s.address = t.event_session_address

                    WHERE s.name = N'system_health'
                        and target_name = 'ring_buffer'
                    ) base
        CROSS APPLY target_data.nodes('/RingBufferTarget/*') td(r);
        GO

        select 
            event_name, 
            dateadd(HOUR,-5,event_timestamp) event_timestamp, 
            wait_type, 
            wait_duration, 
            sql_text, 
            event_data                  
            from #xe
            order by event_timestamp desc
        GO
        DROP TABLE #xe;

SQL Server 2012 offers more information in it's system_health event session. I have a set of queries here that will parse most of the the constituent components.

Depending on what you're seeking, though, the information may not be contained in system_health unless it's waited longer than 15 seconds for a latch or other resource, it's waited for longer than 30 seconds on a lock, or if it's been waiting for something outside of the SQL Server API for longer than 5 seconds (a preemptive wait).

In your MS SQL Server Management Studio

TOOL -> SQL Profiler

There are some minor details about choosing rows/commands and columns but a quick read about it on MSDN and you can start to see what is being sent to the engine to be executed.

Obs:. It ill sniff ANY command sent to the engine from any user and application for any DB on that server.

SQL Server Profiler shows all queries executed against a SQL Server instance. The queries can be executed by a user, application, SQL Server itself,etc.

To be able to see the queries, a SQL trace must be configured and running:

1. Start SQL Server Profiler
2. On the File menu, select New Trace
3. In the Connect to Server dialog, select the SQL Server instance and provide credentials
4. Click Connect
5. In the General tab, specify the trace name
6. Open the Events Section tab
7. Select the Show All Events check box. Make sure that the event type you want to audit is selected