Comprehensive Guide to Viewing Executed Queries in SQL Server Management Studio

Core Functionality and Application of SQL Profiler

In the SQL Server Management Studio environment, viewing executed queries is essential for database administration and performance tuning. SQL Profiler, as Microsoft's official powerful tool, can capture and record all Transact-SQL statements sent to a SQL Server instance in real-time. The tool operates through an event tracing mechanism and can be configured to monitor query activities for specific databases, users, or applications.

To use SQL Profiler, first launch the tool from the SQL Server installation program. In the Profiler interface, create a new trace session and select the event categories to monitor. For query monitoring, key events include <span style="font-family: monospace;">SQL:BatchCompleted</span> and <span style="font-family: monospace;">RPC:Completed</span>, which record the complete text and execution statistics of each query batch. By setting filters, you can narrow the monitoring scope, such as capturing only "expensive queries" exceeding specific thresholds or monitoring queries for particular databases.

-- Example: Viewing recently executed queries through system views
SELECT TOP 50 
    deqs.last_execution_time AS [Execution Time],
    dest.text AS [Query Text],
    deqs.execution_count AS [Execution Count],
    deqs.total_worker_time/1000 AS [Total CPU Time (ms)],
    deqs.total_logical_reads AS [Total Logical Reads]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC;

Limitations of Activity Monitor

The built-in Activity Monitor in SQL Server Management Studio provides a "Recent Expensive Queries" report feature, but this tool has significant limitations. Activity Monitor is primarily designed for real-time monitoring of current server activity rather than providing complete historical query records. Its "Recent Expensive Queries" section typically displays only queries with high resource consumption, potentially missing numerous ordinary or low-resource queries.

Another limitation of Activity Monitor is its short data retention period, where query information may be quickly overwritten by new server activities. For requirements involving auditing or analyzing historical query patterns, Activity Monitor's functionality is clearly insufficient. However, for rapid diagnosis of current performance issues, particularly identifying queries consuming significant system resources, Activity Monitor remains a useful tool.

Alternative Approach: Transaction Log Analysis

For queries that have already completed execution and were not captured by real-time monitoring tools, partial query history can be recovered by analyzing database transaction logs. This method mainly applies to Data Manipulation Language (DML) and Data Definition Language (DDL) commands such as INSERT, UPDATE, DELETE, and CREATE, ALTER statements. SELECT queries typically do not leave traceable records in transaction logs.

To use this method, the database must be in full recovery mode to ensure transaction logs contain sufficient detail. The DBCC LOG command or undocumented system function fn_dblog can be used to read log contents, but their output formats are relatively raw and require expertise for parsing. Third-party log reading tools like ApexSQL Log offer more user-friendly interfaces but usually require paid licenses.

-- Example: Reading transaction log fragments using fn_dblog
SELECT 
    [Current LSN],
    [Operation],
    [Context],
    [Transaction ID],
    [AllocUnitName],
    [Page ID],
    [Slot ID],
    [RowLog Contents 0]
FROM fn_dblog(NULL, NULL)
WHERE Operation IN ('LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS')
ORDER BY [Current LSN] DESC;

Tool Availability Across SQL Server Editions

The availability of SQL Profiler depends on the SQL Server edition and license type. In full editions of SQL Server (such as Standard, Enterprise, and Developer editions), SQL Profiler is installed by default as part of the SQL Server toolset. However, in free editions like SQL Server Express, SQL Profiler is not included officially.

For users of SQL Server Express, third-party free alternatives such as AnjLab's SQL Profiler can be considered. These tools typically offer similar functionality but may differ in performance, stability, and support. When selecting alternative tools, compatibility with specific SQL Server versions and whether they meet monitoring requirements should be evaluated.

Best Practices and Implementation Recommendations

In practical applications, the choice of query monitoring method should consider specific requirements. For development environments and performance tuning scenarios, SQL Profiler is the most comprehensive solution and can be configured to record all queries or queries matching specific patterns. Creating template trace definitions containing commonly used events and columns is recommended for quickly starting monitoring sessions.

For production environments, running full traces continuously may impact server performance. In such cases, Extended Events can be considered as a lighter-weight alternative, or periodic sampling traces can be set up. Additionally, combining system Dynamic Management Views (DMVs) like sys.dm_exec_query_stats can provide historical aggregated query performance data without requiring continuous tracing.

Regardless of the method used, appropriate auditing policies should be established, clearly defining the types of queries to monitor, retention periods, and access permissions. For environments with strict compliance requirements, more complete SQL Server auditing features or specialized database activity monitoring solutions may be necessary.