Optimizing SQL Server code in Toad for SQL Server

Hello. My name is Robert Pound. And I am a technical marketing manager. Creating well written, efficient SQL code depends on several factors, including database architecture, environment, complexity of the request, and the skill of the developer. Thankfully, SQL Optimizer that is included in the Toad for SQL Server Development Studio can assist in your SQL efficiency. For this video it is assumed that you are a developer and you have installed Toad for SQL Server Developer Suite on the system that you intend to develop from. Once Toad for SQL Server is installed on your system, you can utilize SQL Optimizer by simply right clicking on any database in your SQL Server Management Studio. And choosing Toad Optimize and finally Optimize SQL. This will open up the Toad tool and then SQL Optimizer. Of course, you can also directly open up Toad and SQL Optimizer from the environment. You will be presented with two options. We will look at SQL Rewrites. But note you can also choose optimize your SQL by using Plan Control. Here you can see the organization of the tool, where optimize SQL is the task that we are doing. SQL rewrite 1 is the project that is now opened. And SQL detail will display the SQL that we want to tune once it's loaded into the tool. So from here we'll simply open from a file and choose the SQL that we want to tune. Now you can SQL is in the Preview pane. And we have different options for running in optimization. However, we will simply choose Auto Optimize. Before the optimization begins, you'll be presented with a series of questions. This helps the optimizer tune the optimization that happens. But we will just choose the defaults and choose Start Auto Optimize. The tool analyzes syntax of the current statement as well as the associated tables and creates alternate SQL statements to test against the database. As alternates are run, the current champion will be moved to the top of the list. Several alternates will be evaluated. And this may run for several minutes, depending on the complexity of the code. Once the analysis is complete, you're presented with a summary of the actual original SQL, breaking down the statistics, SQL information as far as how its optimized, complexity, et cetera, and then the results at the bottom of the screen. To get a more detailed look at the actual alternates themselves, we can come up to the Compare tab. And it will list out the original as well as the alternate and how it's affected. So for instance, here at the bottom in this code, we can see that everything in the FROM clause obviously has been modified because it's highlighted in green here. It also has details in the Explain Plan itself as far as how it's affected. So from here, you may want to formally report on your findings. We can come to the Report tab. And this will give us a report generating the metrics themselves, as well as the SQL code. This may be useful for team reviewing, etc. So from here, once you have reviewed with the team, you can simply copy the new code, either from the Report or from the Compare tab. From here, you can utilize features such as Compare and Sync function that are highlighted in the video link below. For more information go to www.toadworld.com for the Toad family of products. Hopefully this was helpful for you. Thank you for watching.