How do I view executed queries within SQL Server Management Studio?

Question

I am new to SQL Server Management Studio and am wondering: is there is a way to see which queries have been run against a database?

In the Activity monitor, there is a "Recent Expensive Queries" report but I'm guessing that isn't all of the queries since I'm not seeing the ones I have run.

I am running SQL Server Express 2008 v 10.0.1600.22.

Immateriality

A now deleted answer below points to this blog entry wich shows an SQL query to show the most recent SQL queries. – Lenssen

Answer 1

Use SQL Profiler and use a filter on it to get the most expensive queries.

Marutani

I forgot to mention this is SQL Server Express. From reading other posts, it looks like Profiler isn't included in Express. Is that still correct? – Immateriality

Yes, it is only included with Enterprise, Standard, and Workgroup editions. You can compare this and other features here: microsoft.com/sqlserver/2008/en/us/editions-compare.aspx – Marutani

Answer 2

Use the Activity Monitor. It's the last toolbar in the top bar. It will show you a list of "Recent Expensive Queries". You can double-click them to see the execution plan, etc.

Courtund

+1 for telling us where that otherwise hidden function is and how to use it. – Flight

Answer 3

If you want to see queries that are already executed there is no supported default way to do this. There are some workarounds you can try but don’t expect to find all.

You won’t be able to see SELECT statements for sure but there is a way to see other DML and DDL commands by reading transaction log (assuming database is in full recovery mode).

You can do this using DBCC LOG or fn_dblog commands or third party log reader like ApexSQL Log (note that tool comes with a price)

Now, if you plan on auditing statements that are going to be executed in the future then you can use SQL Profiler to catch everything.

Danielldaniella

Answer 4

You need a SQL profiler, which actually runs outside SQL Management Studio. If you have a paid version of SQL Server (like the developer edition), it should be included in that as another utility.

If you're using a free edition (SQL Express), they have freeware profiles that you can download. I've used AnjLab's profiler (available at http://sites.google.com/site/sqlprofiler), and it seemed to work well.

Isostasy

Answer 5

Run the following query from Management Studio on a running process:

DBCC inputbuffer( spid# )

This will return the SQL currently being run against the database for the SPID provided. Note that you need appropriate permissions to run this command.

This is better than running a trace since it targets a specific SPID. You can see if it's long running based on its CPUTime and DiskIO.

Example to get details of SPID 64:

DBCC inputbuffer(64)

Aflutter

Answer 6

     SELECT *  FROM sys.dm_exec_sessions es
  INNER JOIN sys.dm_exec_connections ec
      ON es.session_id = ec.session_id
  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) where es.session_id=65 under see text contain...

Weidar

you get only sql running within ssms (sql management studio), but can't catch sql running in any dotnet application target ado.net. Only sql profiler can do. – Blemish

Answer 7

If you want SSMS to maintain a query history, use the SSMS Tool Pack add on.

If you want to monitor the SQL Server for currently running queries, use SQL PRofiler as other have already suggested.

Flareup

Answer 8

More clear query, targeting Studio sql queries is :

SELECT text  FROM sys.dm_exec_sessions es
  INNER JOIN sys.dm_exec_connections ec
      ON es.session_id = ec.session_id
  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) 
  where program_name like '%Query'

Materials

you get only sql running within ssms (sql management studio), but can't catch sql running in any dotnet application target ado.net if you change program_name. Only sql profiler can do. – Blemish