The best way to monitor what is being used to either set up a custom server-side trace to capture queries as they occur. You can also look at the default trace that's running on the server, but that's only going to have recent data available. Another option is to query the dynamic management function sys.dm_exec_sql_text. That will show you what's been running and is still in cache.
Of the options, a custom server-side trace will give you the most direct control and flexibility. My second choice would be the DMV's.
answered Nov 09, 2009 at 11:25 AM
Grant Fritchey ♦♦