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.

SQL SERVER - SQL Server Management Studio New Features sql-features-1

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)

SQL SERVER - SQL Server Management Studio New Features sql-features-2

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.

Solarwinds

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-3

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-4

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-5

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-6

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-7

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-8

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.

SQL SERVER - SQL Server Management Studio New Features sql-features-9

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.
Next Post
SQL SERVER – Maximum Number of Index per Table

Related Posts

16 Comments. Leave new

  • Dejan Grujic
    July 2, 2009 2:36 pm

    Dave,

    I don’t know if you noticed, but Intellisense in Query Editor works only when you connect to SQL Server 2008. It worked with older versions while 2008 was in beta, but Microsoft turned it off in final version.

    Since it’s hard to live without Intellisense once you tried it, you can take a look at DbOctopus.

    Among other features it has intellisense which works with older versions of SQL Server too.

    Btw. we’re currently giving away free licenses, so it’s practically a free tool for now.

    Reply
  • Thansk for your article Pinal. It is really nice and give more guide about the new features available with Sql Server 2008.

    Reply
  • one of the thing i notice about the IntelliSense is not work when i use database of sqlserver 2000 in sql 2008 management studio i cant understand that thing.

    Reply
  • Can you please let me know where to download this application?

    Reply
  • Hi Dave,

    This is really very nice topic and you explained very simply. could you please elaborate how to check the performance of the SP using SSMS 2008.

    Anish

    Reply
  • Sumit Thapar
    July 7, 2010 10:07 am

    Thank you Mr.Pinal for such an important and educative knowlegde on new features in sql server 2008.

    Reply
  • We have noticed that in SSMS 2008 when you open a table to display data, the tab text (or MDI Title) does NOT contain the table name as it did in 2000 and 2005. It there anyway to get it to behave the old way? (I’ve been through the ToolsOptions but haven’t found anything).

    This is a major pain for us and we are wondering why MS would remove this display option.

    Thanks in advance,

    Ron Thomas

    Reply
  • I am just wondering why regions can be implemented with same syntax as used in Visual Studio, this style is great PITA because it doesn’t work in VS.

    Whats wrong with this syntax :

    #region RegionType 1

    #endregion

    Reply
  • Thansk for your article Pinal. It is really nice and give more guide about the new features available with Sql Server 2008.

    Reply
  • I also like last one ACtivity monitor

    Reply
  • Can you please help. I have receive this error on my SQL server 2008 management studio after i did the Drop trigger in SQLCMD

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error 0 – The Local Security Authority cannot be contacted) (Microsoft SQL Server)

    Please help.

    Reply
  • It’s good article help a lot

    Reply
  • valueable notess,,,keep posting:)

    Reply
  • valueable notess,,,keep posting again & again:)

    Reply
  • thanks for all articles ur posting…..is there any place to get all the features of 2008 and comparision with 2005…..if it thr please share with us,it will be helpfull….

    Reply

Leave a Reply

Menu