Questions about SQL Scan and Optimizer regarding impact to PROD and what is saved where. (4249046)

This article covers the following questions about the SQL Scan and SQL Optimizer features.

What is the impact in PROD databases when running?

Where are the results stored? 

Does it creates any files in Program Files/Quest Software/Quest SQL Optimizer folder?

How many SQL sessions do the processes generate? 

What other events are created in the database (traces for example) and how many?

Do they store any information in the database?

For SQL Scan, it would spool the text into a local file according to the scanning job type when a job is sent to scan. Then it will check the syntax word by word. If the syntax is matched, it would send this SQL statement to the database to get an explain plan. Then output this statement. If there are multiple scanning jobs, those jobs would be scanned in multiple threads. This is the whole process for the scanner. For impacting the PROD database, it will create one session for getting explain plans during the scanning process. Commonly, it would not have a big impact on the PROD database. But, if there are too many scanning jobs and too many SQLs, it might impact the performance of Prod database.

For the SQL storing, all SQLs are saved into a local file, which you can find in C:\Users\\AppData\Roaming\Quest Software\Quest SQL Optimizer for SQL Server\\Scan SQL Data. The spooling text file is saved into this folder as well. It will be deleted after the scanning process is finished. It will not create any events or save any information into the PROD database during the scanning process.

For SQL Optimizer, it will parse the SQL statement first, then rewrite those SQL statements according to the rules when a SQL statement is sent to optimize. Then it would send the rewritten SQL to get an explain plan and execute them in the database.  It will create 3 sessions for each SQL Optimization process. One is for getting an explain plan. The second one is for executing the rewritten SQLs. The third one is for other use. All information would be saved into a local folder as well, it is: C:\Users\\AppData\Roaming\Quest Software\Quest SQL Optimizer for SQL Server\\Optimize SQL Data. Same as Scanner, it would not create any events and save any information into the database.