This article will provide an overview of the SSMS 18.0 with particular focus on improvements to the SQL execution plan feature. To work with Microsoft SQL Server Database Engine, you need to have an environment to edit, debug and deploy scripts written in different languages such as T-SQL, DAX, MDX, XML and JSON. In addition, you need a GUI tool that helps you to configure, query, monitor and administrate your SQL Server instances wherever they are hosted; locally at your machine, on a remote Windows or Linux server or in the cloud. All this can be achieved using SQL Server Management Studio aka SSMS.
If you are planning to jump from SSMS 2016 and earlier versions to the new SSMS version, it is the good time to know that, Microsoft started a new versioning mechanism for SSMS starting from SQL Server 2017 by separating the SSMS version number from the main SQL Server Engine version number. For example, the version number for SQL Server 2017 starting with 14.0, and the SQL Server 2019 has version number starting with 15.0. On the other hand, the version number of SQL Server 2017 SSMS has version number starting with 17.X and the version number of the SQL Server 2018 SSMS has version number starting with 18.X.
Also, the SSMS installation media cannot be found under the SQL Server Engine installation media, starting from SQL Server 2016. In order to download the latest version of SSMS, which is SSMS 18.0 preview, or the previous versions, you need to download it from the Microsoft Download page directly. SSMS 18.0 installation media has smaller download size (~400 MB), which is less than half of what SSMS 17.x installation media.
After downloading it, you can easily install it to your machine. And with this new release of SSMS, you can install it in a custom folder when installing it using the command line setup. You need to provide the custom path to SSMS-Setup-ENU.exe, SSMSInstallRoot = ‘Custom Location’ command. You can also install it using the SSMS Installation Wizard by running the installation media and clicking on the Install button to start the installation process, that will complete automatically if no reboot is required for the current machine. Take into consideration that the new SSMS 18.0, that has been ported to the VS 2017 Isolated Shell, will not upgrade or replace the already existing SSMS versions, it will be installed side by side with the existing SSMS versions and both will be available for use, as shown below:
The build number that is assigned to SSMS 18.0 (Preview) equal to 15.0.18040.0, that is clear when you click on the Help menu and choose the About option as shown in the snapshot below:
SSMS 18.0 provides support for almost all features that are available in the SQL Server 2008 version and later, including the SQL Server 2019 preview version, in addition to the newly introduced cloud features available in Azure SQL Database, Azure SQL Managed Instance and Azure SQL Data Warehouse. One exception when trying to connect to the legacy SQL Server Integration service using SSMS 17.X or 18.0 (Preview), that required you to use an SSMS version that aligned with the version of the SQL Server Integration Service. SSMS 18.0 supports different Windows Server 64-bit platforms, starting with Windows Server 2008 R2 and later, with its latest available service pack and Windows 10 64-bit client platform.
In SSMS 18.0, Microsoft provides fixes to number of bugs in the previous SSMS versions, includes XEvents, Backup, Restore, Attach and Detach options and more. In addition, SSMS 18.0 provides improvements to number of existing features, includes changing the Storage Account Key authentication mode to Azure AD mode, introducing the AUTOGROW_ALL_FILES Filegroup options, rehashing the Estimated Recovery Time and Estimated data loss in the Always On dashboard, extend SMO support for Resumable Index Creation, improvement to the scripting performance, and SQL Execution Plan improvements. Microsoft announced that in SSMS 18.0, the Database Diagram and T-SQL Debugger features have been deprecated. For complete list of the fixed bugs, improved and deprecated features, check SSMS 18.0 public preview released.
In this article, we will concentrate on the SQL Execution Plan improvements in SSMS 18.0 (Preview) version.
Extra Plan Operator Node Information
When checking the Actual SQL Execution Plan generated after executing your query in SSMS 18.0, you will see that Microsoft added extra information about each node operator. This information includes the Actual Time Elapsed and Actual vs Estimated rows with accuracy percentage values, that can be shown under each plan operator node if they are available.
This will be clear when running your query using SSMS 17.7 version, including the Actual SQL Execution Plan for that query. You can see from the generated SQL Execution Plan that, only the cost of each operator related to the overall query weight will be presented, as shown below:
On the other hand, if you run your query using SSMS 18.0 (Preview 4) version, including the Actual SQL Execution Plan for that query, you will see the extra information listed under each operator, that includes the Actual Time Elapsed in seconds, the number of Actual rows, the number of Estimated rows and the Estimated to Actual number of rows accuracy percentage, as shown clearly below:
This extra information makes the actual plan looks consistent with Live Query Stats plan. In addition, it makes the Actual SQL Execution Plans more readable plan, especially when dealing with large plans that have large trees. In other words, this information will save your time browsing and analyzing the tooltips for each plan operator, by viewing the most important and useful information directly to the users.
Query Text Truncate Comment
In SSMS 18.0, Microsoft included a new comment in the upper portion of the SQL execution plan, when moving the mouse pointer or clicking on the Edit Query Button for the SQL Execution Plan, that the text of the submitted query may be truncated. This comment helps to inform the user that, if the query text exceeds the 4000 characters, it will be truncated by the SQL engine. This was occurring with me previously while editing long queries, but this time SSMS informs us about this truncation in advance, as shown below:
Other SQL Execution Plan Improvements
- In SSMS 18.0, Microsoft added a new logic to the SQL Execution Plans to display the “Materializer Operator (External Select)”. A materialize node will be displayed when the output of lower part of the plan is materialized into the memory before executing the upper node, as the source of the upper node needs to be rescanned.
- In SSMS 18.0, Microsoft added a new attribute, called BatchModeOnRowStoreUsed, to make it easy to identify the queries that are using the “batch-mode scan on rowstores” feature. The Batch Mode over Rowstore feature can be enabled by simply switching the database to the latest compatibility level of SQL Server 2019, which is 150. Whenever a batch-mode scan is performed on rowstores in your query, a new attribute (BatchModeOnRowStoreUsed=”true”) will be added to StmtSimple element.
Table of contents
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021