In the late hours of the night change tracking is showing queries being run from the middleware server. The Db user executing the queries is from the DB user we use to run the Quest agents.
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.
Performance Analysis for SQL Server as part of its change tracking feature will run estimated execution plans against the monitored SQL Server.
Whenever PA executes a user SQL, especially if it does so at off-peak hours (typically around midnight) that this is a change tracking operation. The user can always be shown the negligible impact of "executing" these statements by using Performance Analysis History View to see the resource consumption of the statements.
You will see that Performance Analysis is in fact not actually running these SQL's and only getting the estimated execution plans.