SQL Server Agent Missing in SSMS – How to Find It

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

In the past I wrote some scripts to capture slow running queries.

I used SQL Profiler via T-SQL stored procedures. The overhead for this wasn't huge and at the time provided valuable insight.

More info and some examples are here: http://www.toadworld.com/platforms/sql-server/w/wiki/9827.running-traces-with-tsql.aspx

I would write two stored procedures, one to start the trace and one to stop it. These could be called via a SQL Agent job or a scheduled task, whatever is preferred. Log only the info you want to a table rather than files for easier management. You could create a third job to clean up records after "X" days/weeks/months depending on your requirements.

The performance hit will depend on the existing load on the server and there is no easy way of doing this so test any recommended solutions thoroughly before deciding which way to go.