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.

A now deleted answer below points to [this blog entry](http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/) wich shows an SQL query to show the most recent SQL queries. — Uwe Keim, Jan 26 '16 at 17:44

score 22 · Accepted Answer · answered Mar 15 '10 at 18:56

22

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

answered Mar 15 '10 at 18:56

Benjamin Ortuzar

7,801
6
41
46

2

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? – bsh152s Mar 15 '10 at 19:06
Yes, it is only included with Enterprise, Standard, and Workgroup editions. You can compare this and other features here: http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx – Benjamin Ortuzar Mar 15 '10 at 19:28

score 17 · Answer 2 · edited Feb 11 '22 at 06:13

17

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.

edited Feb 11 '22 at 06:13

Loathing

5,109
3
24
35

answered Mar 15 '10 at 19:02

John Saunders

160,644
26
247
397

4

+1 for telling us where that otherwise hidden function is and how to use it. – cjbarth Aug 16 '13 at 22:52

score 15 · Answer 3 · answered Jun 11 '13 at 09:46

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.

score 10 · Answer 4 · answered Mar 15 '10 at 18:58

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.

score 4 · Answer 5 · answered Apr 25 '13 at 06:29

4

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

answered Apr 25 '13 at 06:29

Vishe

3,383
1
24
23

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. – M.Hassan Jul 20 '16 at 20:05

score 4 · Answer 6 · answered Sep 05 '11 at 15:57

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)

score 3 · Answer 7 · answered Mar 15 '10 at 19:17

3

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.

answered Mar 15 '10 at 19:17

Remus Rusanu

288,378
40
442
569

score 3 · Answer 8 · answered Jun 04 '15 at 12:41

3

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'

answered Jun 04 '15 at 12:41

zhrist

1,169
11
28

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. – M.Hassan Jul 20 '16 at 20:07

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

8 Answers8

Linked