How to Monitor the Executed SQL Statements on a SQL Server 2005

How can I monitor the executed sql statements on a SQL Server 2005

Seeing how you use the Management Studio Express, I will assume you don't have access to the MSSQL 2005 client tools. If you do, install those, because it includes the SQL profiler which does exactly what you want (and more!). For more info about that one, see msdn.

I found this a while ago, because I was thinking about the exact same thing. I have access to the client tools myself, so I don't really need to yet, but that access is not unlimited (it's through my current job). If you try it out, let me know if it works ;-)

Is there a tool to monitor the SQL statements being executed by an .EXE?

If you have the appropriate rights (sysadmin or ALTER TRACE permission) on the DB you could watch using SQL Profiler.

SQL Server activity monitor show all queries

To watch every query sent to SQL server using SQL profiler, you can use one of SQL Profiler predefined templates. It's TSQL_Duration.

It's designed to track completed stored procedures and TSQL code.

The trace will show you exactly the batch executed.

You can expand the events tracked and columns shown.You can find more information here: How To: Use SQL Profiler

The same feature is also available in ApexSQL Comply, an auditing tool that uses SQL traces to capture the events, then stores them in a central repository database, and provides built-in and custom reports where you can easily filter the transactions, and export them as Excel, Word, or PDF files.

Disclaimer: I work for ApexSQL as a Support Engineer

Monitoring the progress of an SQL query in SQL SERVER

There is no way to know how much time is left. A query's runtime depends on many things beyond the actual query itself: locking/blocking of other queries, other processes consuming resources (CPU/disk usage), the operating system, network, etc. What if your 5-minute query is running, yet someone else kicks off a large report, your query may run 5:30 now. What if the someone starts to download a large file and hogs all the network bandwidth? What if the OS decides to do something in the background, etc. Until all the rows are returned, the query isn't done, but it can run in a variable time frame.

List the queries running on SQL Server

This will show you the longest running SPIDs on a SQL 2000 or SQL 2005 server:

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

If you need to see the SQL running for a given spid from the results, use something like this:

declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    sys.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING(  text,
            COALESCE(NULLIF(@stmt_start, 0), 1),
            CASE @stmt_end
                WHEN -1
                    THEN DATALENGTH(text)
                ELSE
                    (@stmt_end - @stmt_start)
                END
        )
FROM ::fn_get_sql(@sql_handle)

SQL Server 2005 Express - Activty Monitor - Show details is empty, how can I tell what the SQL statement that started the process is?

Try using the following query.

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
   st.text,
   qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

Then may I suggest that you get yourself a copy of the awesome sp_WhoIsActive stored procedure written and shared for free courtesy of Adam Machanic. You can use the procedure to identify all currently running queries, including a wealth of associated information such as the amount of CPU used, IO, the execution plan, query text, blocking etc.

How to see what SQL statements are executed when using SQL Express?

Use Anjlab it works with SQL Express http://anjlab.com/en/projects/opensource/sqlprofiler

See list of all SQL Queries run on server

No

The only way this can be done is if a monitoring process is set up in advance.

Monitoring a database, e.g. through SQL Profiler, has a performance impact, so one should be wary about using this against a live system for prolonged periods.

A better way to monitor this kind of activity, would be by use of logging from the calling code.

---

Related Topics