SQL SERVER – SQL Server Management Studio New Features

This article describes the top 5 features of SQL Server Management Studio 2008. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

SQL Server Management Studio

SQL Server 2008 has been released for a year now. In SQL Server 2000, DBA had to use two different tools to maintain the database as well as the query database, specifically SQL Server Enterprise Manager and SQL Server Query Analyzer. With the release of SQL Server 2005 both of these tools are combined into one tool: SQL Server Management Studio. For a while DBA who were fans of Query Analyzer requested to bring it back, but as they keep on using SQL Server Management Studio (SSMS) they realized that it was much more convenient if every task related to SQL Server could be accomplished using SSMS. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

Let us go over a few of the important new features of the SSMS 2008. The list of SQL Server 2008 SSMS improvements is very long. I have selected my favorite 5 features and we will go over them.

1. IntelliSense for Query Editing
2. Multi Server Query
3. Query Editor Regions
4. Object Explorer Enhancements
5. Activity Monitors

IntelliSense for Query Editing

This is my favorite feature so far. If you have used Visual Studio before, this feature may not be new to you. Implementation of IntelliSense is not as advanced as in Visual Studio but it is still very useful and well implemented.

After implementing IntelliSense, DBAs and developers will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the world. You can see those option from SQL Server Management Studio Menu >> Edit >> IntelliSense >> (See the available options)

There is a total of five options available in the Edit Menu. I suggest to experiment with all of them while playing with IntelliSense.

1. List Members
2. Parameter Info
3. Quick Info
4. Complete Word
5. Refresh Local Cache

If your IntelliSense does not bring up recently created objects, try “Refresh Local Cache” as described above, or press CTRL + SHIFT + R.

IntelliSense is a new feature and it will take some time to get adjusted to it. If any developer does not like this option, it can be turned off from Menu >> Query >> (deselect) Enable IntelliSense.

MultiServer Query

Usually DBA don’t manage only one database; they have many servers to manage. There are cases when DBA has to check the status of all the servers. I have seen one of the DBA who used to manage 400 servers, writing query using XML_CMDSHELL where he wanted to find out what the status of full back up on all the servers was. In one of the recent consultancy job, when I had to find out if all the three servers were upgraded with Services Packs (SP), I ran a query to find version information on all the three instances separately in three windows.

SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all make sure that you registered all the servers under your registered server. Once they are registered Right Click on server group name and click New Query as shown in the image below.

Now in the opened query window run the following query (you can find it in the sample code for this article):

SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion

The query above will give the result shown in the image below. Note that we have only three columns in the SELECT but our output contains four columns. The very first column is the “Server Name” and it is added by SQL Server to identify rows belonging to a specific server.

If all of the above servers are registered with “central server” – the option which is right below it, other administrators can also register to those entire servers by simple registering one central server.

Query Editor Regions

This feature exists in many programming languages already, but now it is newly introduced in SSMS 2008. The reason I am highlighting this feature is because there are cases when T-SQL code is longer than hundreds of lines and after a while it keeps on getting confusing.

The regions are defined by the following hierarchy:

1. From first GO command to next GO command.
2. Statements between BEGIN – END, BEGIN TRY – END TRY, BEGIN CATCH – END CATCH
3. Multiline statements

In the following code snippet I have reported an example which has five coding regions (you can find it in the sample code for this article). I have made a small comment besides them describing their region type.

CREATE PROCEDURE MyTest -- Region Type 1
AS
BEGIN --Region Type 2
SELECT 1
BEGIN -- Region Type 2
SELECT 2;
SELECT * -- Region Type 3
FROM sys.objects;
SELECT * -- Region Type 3
FROM sys.columns;
SELECT 3;
END
END
GO 

Regions can be collapsed as well as expanded by clicking the small sign of ‘-’ or ‘+’ besides them. The following image shows a fully expanded region code snippet and a fully collapsed code snippet.

If there is any single-line statement, it does not qualify for a coding region.

Object Explorer Enhancements

Object Explorer Detail initially looks the same as the previous version, but when right clicked on the bar with labels it reveals what it can do. This feature looks the same as the Vista OS folder option but when looked at how it is implemented for SQL Server data, it is really amazing. The Object Explorer Detail view can be enabled by either going to Menu >> View >> Object Explorer Detail or pressing F7.

In Object Explorer Detail the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.

Additionally, there are new wizards which help you perform several tasks, from a policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted into Excel without any formatting issues.

Activity Monitors

This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

There are four graphs 1) % Processor Time, 2) Waiting Tasks, 3) Database I/O, 4) Batch Requests/Sec

Additionally, there are four tabs, which also reflect some statistics of the above four graphs 1) Processes, 2) Resource Waits, 3) Data File I/O, 4) Recent Expensive Queries. Clicking on the tables will expand the tabs as shown in the following image.

All the four tabs provide very important information, however the one which I refer most is ‘Recent Expensive Queries’. Whenever I find my server running slow or having any performance related issues my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment.

Summary

There are lots of new features of SQL Server Management Studio 2008. The ones I have listed today are the most prominent and my personal favorites.

Reference : Pinal Dave (https://blog.sqlauthority.com)