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 or Microsoft Access. 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 application names that are not like the name of your production ...
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
