The previous article titled New Features in SQL Server 2008 Management Studio – Part 1 discussed some of the new features available in SQL Server 2008 Management Studio; namely Activity Monitor, Intellisense Support and Region Outlining. This article continues to explore the new features available within SQL Server 2008 Management Studio (SSMS). These features can be used by database developers and database administrators in their day to day activities.
New Features & Enhancements in SSMS
This article will discuss the following new features:
- Object Explorer Details
- Object Search
- View Object Dependencies
- Multi-Server Query Support
- Missing Index Hints in Graphical Execution Plan
Object Explorer Details
The Object Explorer Details window in the previous versions of SQL Server used display similar information to what was displayed in the Object Explorer. Thus it was not of great benefit for database developers and administrators. However in SQL Server 2008 there have been new enhancements made to the Object Explorer Details window. The detailed information displayed in depends on the object selected within the Object Explorer. Open the Object Explorer Details window in SQL Server 2008 by pressing F7 key or selecting “Object Explorer Details” option from the View menu. In the below snippet see that in the Object Explorer Details windows information related to all the System Databases is displayed, as it has been selected on the left drop-down menu.
As observed in the Object Explorer Details window, there is lot of information available such as Name of system databases, Policy Health State, Recovery Model, Compatibility Level, Collation, Owner etc. By default when Object Explorer Details is opened a maximum of five columns are displayed. There are approximately 36 columns available within this window. To explore all the different columns, right click any of the columns and select the desired column from the popup menu as shown in the below snippet.
If adding columns in Object Explorer Details, those columns will be available next time the Object Explorer Details window is opened. All user specific settings are automatically saved by SQL Server.
Object Search
Object Search is a feature available within the Object Explorer Details window. Provide the name of the object within the search textbox as shown in the below snippet and press enter to start the search. The snippet below displays a search for an object named “contact” within the AdventureWorks database. To search for an object within all the databases then select Databases Node within the Object Explorer and then provide the object name to search within the search textbox. As seen below, once the search has completed successfully it will display the list of all the objects found including the path where it is can be located within the database.
To access the object, right click the search result and then click Synchronize from the popup menu. It will directly go to the location of the object.
View Object Dependencies
After the object has been located it’s prudent to identify the dependencies for the object before performing any modify or delete operations. Find these by right clicking the object from the search results and selecting View Dependencies option from the popup menu as shown in the below snippet.
Below note the view for dependencies of the “contact” object in the AdventureWorks database. Most importantly there are two options available, one for finding “Objects that depend on [Contact]” and another for “Objects on which [Contact] depends.” This simplifies matters for the developer who wants to modify or delete objects.
Multi-Server Query Support
Using SQL Server 2008 Management Studio (SSMS), it is possible to execute TSQL code against multiple SQL Servers simultaneously. This is a useful feature for database administrators who need to manage lot of SQL Servers within an organisation. To use this feature, firstly create a group with all the register SQL Servers as shown in the below snippet. Once all the SQL Servers which to be managed are registered under a particular user group, the next step is to right click the user group and click New Query as shown in the below snippet to execute the query against all the servers which are part of that group.
In the New Query window, execute the query below to identify the SQL Server Edition, Product Level, Product Version and SQL Server Default Collation Server Properties.
SELECT SERVERPROPERTY(‘Edition’) AS Edition
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘Collation’) AS Collation
GO
When executing the above query, internally the SQL Server runs it against all the SQL Servers which are registered under the group independently and the results are merged and displayed. To change how results are displayed then go to Tools > Options > Query Results > SQL Server > MultiServer Results and make the changes as appropriate.
To learn more about this feature then refer to the article titled How to configure and Use the Central Management Server Feature in SQL Server 2008.
Missing Index Hints in Graphical Execution Plan
It has always been challenging for database administrators to identity missing indexes for a table. In SQL Server 2005, Microsoft introduced Dynamic Management Views (DMV’s) which return SQL Server state information which can be used by database administrators and database developers to monitor the health of an SQL Server Instance and identify potential performance issues. Dynamic Management Views reflect all the activities on the instance of SQL Server since the last restart of SQL Server. The article titled Identify Missing Indexes Using SQL Server DMVs discussed about how to use DMV’s to identify missing indexes. In SQL Server 2008 Management Studio there is an excellent feature to identify missing indexes when executing the TSQL code within the Query window. To use this feature click “CTRL+M” or select “Include Actual Execution Plan” option from the Query menu before executing the query. In the example below a query is running against the Sales.Store table of the AdventureWorks database.
Sample TSQL Query
USE AdventureWorks
GO
SELECT Name,CustomerID,ModifiedDate FROM Sales.Store
WHERE (Name=’Sharp Bikes’ AND CustomerID <> ” AND ModifiedDate > ‘2004-10-01’)
GO
Once the query has executed successfully, click on Execution Plan as shown above. SQL Server 2008 will display information related all the missing indexes. Connect from SQL Server 2008 Management Studio to any SQL Server 2005 databases to view the missing index information from the execution plan. The information displayed within Execution Plan is picked from the data which SQL Server 2005 and 2008 collects using Dynamic Management Views since the last restart of SQL Server Instance.
This feature is useful as it displays the missing index information and also provides the script which can be used to create the missing index. Generate the script by right clicking Missing Index and then by selecting Missing Index Details…. option from the drop down as shown in the below snippet.
The below script is generated by the SQL Server 2008 after the Missing Index Details option has been clicked.
/*
Missing Index Details from SQLQuery1.sql – AKMEHTASQL2008.AdventureWorks (akmehta (55))
The Query Processor estimates that implementing the following index could improve the query cost by 95.0908%.
*/
/*
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Sales].[Store] ([Name],[CustomerID],[ModifiedDate])
GO
*/
If interested in accepting the suggestion given by SQL Server 2008, then modify the script by providing an appropriate name for the index and then press F5 to create the index.
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_Name_Sales_Store]
ON [Sales].[Store] ([Name],[CustomerID],[ModifiedDate])
GO
Conclusion
This article has demonstrated how database developers and database administrators can benefit from new features like Object Explorer Details, Object Search, View Object Dependencies, Multi-Server Query Support and Missing Index Hints in Graphical Execution Plan that are available in SQL Server 2008 Management Studio.