Real time preview of SQL queries generated by applications

I need to know what SQL queries are generated by some application.

Is it possible to see it in some kind of sniffer?
I tried to look in Management Studio, but it shows only most expensive queries.

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).

StackExchange Database Administrators Q#63138, answer score: 9