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?
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:
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.
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.
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.
You may want to extend the pattern - for example this will ignore queries that start with
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.