Microsoft® SQL Server 2012 Unleashed

Identifying Ad Hoc Queries

One problem that can plague a production system is the execution of ad hoc queries against the production database. If you want to identify ad hoc queries, the application, and the users who are running them, SQL Profiler is your tool. You can create a trace as follows:

1. Create a new trace, using the SQLProfilerStandard template.

2. Add a new ApplicationName filter with Like Microsoft%.

When this trace is run, you can identify database access that is happening via SSMS, Microsoft Access, and other Microsoft applications that are accessing the database. The user, the duration, and the actual SQL statement are captured. An alternative would be to change the ApplicationName filter to trace application access for all ...