Logging queries and other T-SQL

Question

I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).

If yes, where can I see it? If not, how could I set it up?

Answer 1

By default, SQL Server activity is not logged the way you expect. Some write activity is recorded in the Transaction Log, but this also depends on how your databases are set up.

There are four main options for tracking SELECT activity on a server:

1. You can use SQL Server Profiler to connect to your server and watch for specific activity as it happens.

2. You can create a server-side trace to log activity to a trace file on the server, which can then be read by SQL Server Profiler, or by using fn_trace_gettable to load it into a table for further analysis.

3. You can use Extended Events, which offers more functionality than server-side traces, and which Microsoft recommends instead of server-side traces starting with SQL Server 2012.

4. You could use C2 audit mode.

You can use SQL Server Profiler to set up your trace (choose the specific events, filters, etc. that you want), then script it out using the File menu and execute it on the server to create a server-side trace, as described here.

Answer 2

As long as you don't care who ran the queries, there are some ways to see metrics about queries that are being run in your system. This is limited to data since the last restart and potentially by plan cache (or other memory) pressure depending on how much deeper you extend the query.

;WITH x AS
(
  SELECT 
    [text] = SUBSTRING(t.[text], 
      (s.statement_start_offset/2)+1, 
      COALESCE(NULLIF(s.statement_end_offset,-1),DATALENGTH(t.[text])*2)
       -(s.statement_start_offset/2)), 
    s.execution_count, s.last_execution_time,
    s.max_logical_reads, s.max_elapsed_time
  FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
)
SELECT * FROM x
WHERE LTRIM([text]) LIKE 'SELECT%';

You may want to extend the pattern - for example this will ignore queries that start with ;WITH, ironically, and can capture SELECT INTO and even variable assignments that don't reference actual tables.

However if you need more granular information than that, Nathan's answer is a good start (except if you're going to use trace, do NOT use Profiler). Just remember that logging every single query against your system is not going to be free.

Answer 3

There are several SQL Server solutions and techniques for tracking SELECT statements

1. Specially developed stored procedures and functions – Note: this method requires advanced knowledge of T-SQL programming and additional maintaining of the stored procedures and functions (e.g. in case of the database schema change). You can see more in this article: http://alstechtips.blogspot.com/2011/02/auditing-select-statements-in-sql.html

2. SQL Server trace technology - you can read a step-by-step instruction in this article: http://solutioncenter.apexsql.com/auditing-select-statements-on-sql-server/

3. Using SQL Server Audit feature - The Audit feature (introduced in SQL Server 2008) can track both server and database events, and is using the Extended Events technology. But, database level auditing is only supported by the SQL Server Developer and Enterprise editions.

4. Using 3rd party tools like ApexSQL Audit or Idera SQL Compliance Manager

Answer 4

By default, it does not log SELECT statements. You can refer to my answer here for more details to audit SELECT statements.

Also, by default it does not even log T-SQL statements, instead it logs EVENTS like Server Memory Change, Audit Login Failed, Audit Addlogin Event, etc and you can find more info here along with the T-SQL Scripts to extract information from Default trace.