SQL SERVER – 2008 – 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 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 fan 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 SSMS Menu >> Edit >> IntelliSense >> (See the available options)

There are a total of five options available in 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 fullback 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 instance 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

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 indentify 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 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. 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 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 to Excel without any formatting issue.

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 SSMS 2008. The ones I have listed today are the most prominent and my personal favorites.

Reference : Pinal Dave (http://blog.SQLAuthority.com), dotnetslackers

About these ads

19 thoughts on “SQL SERVER – 2008 – Management Studio New Features

  1. 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.

  2. 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.

  3. 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

  4. 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 Tools\Options 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

  5. 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

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

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31 Journey to SQLAuthority

  8. 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.

  9. 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….

  10. Pingback: SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049 | SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s