5

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.

  • 3
    spy is somewhat misleading .. You can modify your title to profile. – Kin Shah Apr 14 '14 at 13:00
  • @KinShah: is it better now? – apocalypse Jun 15 '19 at 15:09
6

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.

9

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

5

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

enter image description here

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.