In the first part of this series, we will learn how to export SQL statements from Foglight SQL Performance Investigator, and import them into SQL Optimizer.
Start by opening SQL PI, and selecting a database from the Databases dimension. This simplifies things in the coming steps. Pick a time range, select the number of statements to show and select SQL Statements once you've picked a database. Finally, select "Export to CSV" to save the statements.
Open SQL Optimizer, select the Scan SQL tab followed by the Connection settings. Provide the connection details to your SQL Server instance, and select the database that you used in SQL PI for the export.
Right-click on Scanner in the Scanner Explorer -> Task panel and select Source Code.
The wizard walks you through the steps to pick the CSV file you exported from SQL PI.
You can create a new Scanner group, or add the CSV file to an existing group:
The file will be scanned where SELECT, INSERT, UPDATE and DELETE statements will be extracted. The execution plan will be retrieved based on the connection you provided earlier. Then, the execution plan is analyzed according to rules that classify the statement as Simple, Complex or Problematic. If an execution plan could not be retrieved, the statement gets marked as invalid. An example would be if an object referenced in the statement doesn't exist or isn't visible for the connection specified.
Stay tuned for the next post where we will look at the results of the scan and the rules that classify the statements.
You can download a trial of Foglight here. Make sure to get the SQL Optimizer trial while you're at it!
