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 Server 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 ...
Get Microsoft SQL Server 2014 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.
