New SSMS features and enhancements

As we saw with the installation process, there are already a few enhancements in the installation and updating process for SSMS. Through the migration of the SSMS application to the Visual Studio 2015 Isolated Shell, there are a number of additions into SSMS that will be familiar to application developers who use Visual Studio 2015 (or one of its derivatives). While some of these are simple improvements, these additions can be of help to many SQL developers who have been isolated inside SSMS 2016.

Autosave open tabs

The first improvement is the option to choose whether SSMS should prompt to save unsaved tabs when you decide to close SSMS. This is a simple change, but if you use SSMS to run many ad hoc queries and do not want to constantly close out and save each tab, this is now an option. The default is for SSMS to prompt when closing a window, but by unchecking the checkbox marked in Figure 3.7, you can force SSMS to silently close these windows.

Figure 3.7: Options window in SQL Server Management Studio - prompt to save unsaved work

Searchable options

The next usability or productivity enhancement comes via the Visual Studio 2015 Isolated Shell features. The options menu inside Visual Studio, and SSMS, is jam-packed with features and functionalities that can be configured. Unfortunately, these options are so numerous that it can be difficult to navigate and find the option you are interested in. To aid us in the search for the settings we are interested in, we are now able to quickly search and filter in the options menu. The ability to quickly search through the options for settings via a text search without having to memorize where the settings are hidden is shown in Figure 3.8. Go to Tools | Options and you are then able to type your search string in the textbox in the top left of the options window. In Figure 3.8, the search term execution has been entered, filtering the results as the word is typed into the search form.

Figure 3.8: Options window - search/filter

Enhanced scroll bar

A further improvement that will be used on a much more regular basis is the enhanced scroll bar in the T-SQL editor tab. In Figure 3.9 we can see an example of a T-SQL stored procedure that has been opened for editing.

Figure 3.9: SQL Server Management Studio Scroll bar enhancements

The main points to pay attention to are: the margin on the left-hand side of the screen and the scroll bar on the right-hand side of Management Studio. The enhancement here allows us to easily identify a few details in our script window.

• Green blocks show changes made in the editor have been saved to the file currently being edited
• Yellow blocks show changes that have not yet been saved
• Red blocks show code that is invalid or has syntax errors (native IntelliSense must be enabled for this feature to work)
• The blue marker on the scroll bar shows the location of the cursor

These subtle changes are further examples of the Visual Studio base providing us with further enhancements to make working inside SSMS easier. Knowing what code has been changed or is defective on a syntax level allows us to quickly navigate through our code inside SSMS.

Execution plan comparison

Refactoring and improving the performance of code is a regular occurrence in the working day of a developer. Being able to identify whether the refactoring of a particular query has helped improve an execution plan can sometimes be difficult. To help us identify plan changes, SSMS 2016 now offers the option to compare execution plans.

By saving the execution plan and the T-SQL of our initial query as a .sqlplan file, we can then run our redesigned query and compare the two plans. In Figure 3.10, we can see how to initiate a plan comparison.

Figure 3.10: Activating a plan comparison session

Upon activation, we must choose which .sqlplan file we would like to use for the comparison session. The two execution plans are loaded into a separate Showplan comparison tab in SSMS and we can evaluate how the plans differ or how they are similar. In Figure 3.11, we see a plan comparison where there are only slight differences between the plans.

Figure 3.11: Showplan comparison tab

The nodes of the execution plans in Figure 3.11 that are similar have a red background, while nodes that are different have a yellow background.

If we click the nodes inside one of the plans, the matching node in the comparison plan will be highlighted and we can then investigate how they are similar, and how they differ.

Once we have chosen the node in our execution plan, we will be able to view the properties of the node we wish to compare, similar to the details shown in Figure 3.12:

Figure 3.12: Showplan comparison - node properties

The properties tab clearly shows which parts of the node are different. In Figure 3.12, we can ignore the lower inequality, which is stating the Node ID is different, this will occur wherever our query has a slightly changed plan. Of interest in this case is the Estimated Operator Cost property, which is showing a difference. This example is very simple and the differences are minimal, but we are able to identify differences in a very similar plan with a few simple clicks. This sort of support is invaluable and a huge time saver, especially when plans are larger and more complex.

Live Query Statistics

Following on from the plan comparison feature, we have one of the more powerful features for a developer. Live Query Statistics (LQS) does exactly what the name says—it provides us with a live view of a query execution so that we can see exactly how data is flowing through the query plan. In previous versions of SQL Server and SSMS, we have been able to request a static graphical representation of a query execution plan. There have been multiple books, blogposts, videos, and training seminars designed and delivered to thousands of developers and DBAs around the world in an attempt to improve people's ability to understand these static execution plans. The ability of a developer to read and interpret the contents of these plans rests largely on these resources. With LQS, we have an additional tool at our disposal to be able to more easily identify how SQL Server is consuming and processing the T-SQL query that we have submitted to it. The special sauce in LQS is that we don't get a static graphical representation, but rather an animation of the execution. The execution plan is displayed in SSMS and the arrows between the plan nodes move to show the data flowing between the nodes. In Figure 3.13, we see how to activate LQS inside SSMS for a particular query tab.

Figure 3.13: Activate Live Query Statistics

As Live Query Statistics shows a moving image, we are at a distinct disadvantage when trying to visualize it in a book! However, when we run a query with LQS activated, it is still possible to show an example of how LQS looks while running, as we can see in Figure 3.14.

Figure 3.14: Live Query Statistics - Query execution

In Figure 3.14, we can see that the execution plan image that we are used to has been extended slightly. We now see a few extra details. Starting in the top left of this image, we see an estimated query progress in percent. As with anything to do with query execution and statistics, SQL Server is always working with estimations that are based on table and index statistics, which is a topic worthy of an entire book. We also see an execution time displayed below each node that is still actively processing data. Also below each node is a display of how many rows are still left to be processed (these are also based on estimations through statistics). Finally, we see the arrows connecting each node; solid lines are where execution has completed, dotted lines (which also move during execution), show where data is still flowing and being processed.

You can try out the same query as shown in Figure 3.14 and see how LQS looks. This is a long-running query against sys.objects to produce a large enough result set that LQS has time to capture exhaustion information.

SELECT * FROM  
           SYS.OBJECTS AS o1 
CROSS JOIN sys.objects AS o2 
CROSS JOIN sys.objects AS o3 

This sample query should run long enough to allow LQS to display an animated query plan long enough to understand how LQS makes a query plan easier to understand. It should also be clear that LQS can only display a useful animation for queries that for run longer than a few seconds, as the animation only runs for the duration of the query.

This moving display of data flow allows us as developers to understand how SQL Server is processing our query. We are able to get a better insight into where execution is spending the most time and resources and also where we may need to consider re-writing a query or applying different indexing to achieve better results. LQS, coupled with query plan comparisons will allow us as developers to design better database solutions and better understand how SQL Server processes our queries. In particular, how SQL Server must wait for certain nodes in an execution plan to complete before continuing onto the next node.

However, we must not forget: running LQS, similar to running a trace, requires a certain set of permissions and also consumes resources on the server. We should approach our queries with LQS at the development stage and attempt to write optimal code before we deploy it into production. LQS should therefore be used primarily on your development work in a test environment and not in your production environment.