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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to ssms

Examples related to sql-server-express