SQL Server Management Studio (SSMS) is a graphical tool which can be used by Database Developers and Database Administrators to perform development and administrative tasks on SQL Server 2008. SQL Server Management Studio (SSMS) tool was first introduced by Microsoft in SQL Server 2005. SSMS is a replacement for Query Analyzer, Analysis Manager & Enterprise Manager tools which were available in SQL Server 2000. Microsoft consolidated all those tools into a single tool thereby providing an integrated tool that caters the needs of developers and database administrators. As expected, there are lot many new Features and Improvements in SQL Server Management Studio (SSMS) of SQL Server 2008. This is the first of two articles that will cover new features and improvements in SQL Server 2008 Management Studio.
Few New Features & Enhancements in SSMS
Some of the new features and improvements in SQL Server 2008 Management Studio (SSMS) are:
- Activity Monitor
- Intellisense Support
- Region Outlining
Activity Monitor
The SQL Server 2008 Activity Monitor is a great tool which can be leveraged by database administrators to get a quick overview of an SQL Server 2008 systems performance. In the earlier versions of SQL Server the Activity Monitor tool use to display information related to Processes, Lock by Objects and Locks by Process. However there has been lot many changes to Activity Monitor tool in SQL Server 2008. Activity Monitor in SQL Server 2008 includes performance dashboards with graphs, performance indicators with drill down and filtering capabilities which makes easier to find required information for DBA’s. Activity Monitor tool cannot be accessed from Management Node as it was there in the previous versions of SQL Server.
In SQL Server 2008, invoke Activity Monitor in the following ways.
1) Right clicking a SQL Server Instance within Object Explorer and then select Activity Monitor from the pop up window as shown below.
2) Also clicking Activity Monitor button on the standard toolbar as shown below
In SQL Server 2008 Activity Monitor there are five sections like Overview, Processes, Resource Waits, Data File I/O and Recent Expensive Queries as shown in the below snippet.
Overview Section: – the overview section contains four graphs which show real time information related to the SQL Servers Performance. The first graph displays the % Processor Time of all the Processors that SQL Server is using. The second graph is for Waiting Tasks. The third graph represents the Database I/O activities and the fourth graph represents Batch Requests/sec. Prior to SQL Server 2008 to access the above information involved using System Monitors to capture the information and which needed review to understand what is happening at SQL Server System. With the introduction of Overview section in Activity Monitor database administrators can get the real time information quickly as a result they can fix issues whenever server is behaving strangely.
Processes Section: – Database Administrators can click on the Processes section to expand it; to monitor and access the status of any SQL Server Process which is currently running on the system. Activity Monitor displays information related to current processes such as Session ID, User Process, Login Details, Database which is used by the process, Task Status, SQL Command that is run by the process, Application Used, Memory Used, Host Name etc. In the processes section allows for sorting and filtering using the drop down boxes available at the top of each column. A feature of note is to run a SQL Profiler Trace for a specific SPID that is having issues, select that particular SPID and right click the same, and choose “Trace Process in SQL Server Profiler” option. The other two options seen are “Details” to know more about an individual SPID and the other option is “Kill Process” to kill the respective process.
Resource Waits: – provides information related to key resources waiting on the server. Resources Waits measures the amount of time a SQL worker thread needs to wait until it can get access to the other resources of SQL Server like Memory, CPU etc. If there is a very high resource wait time indicated then there is a resource bottleneck needing to be resolved. In Resource Wait section each column can be sorted and filtered. The different resource waits captured are Backup, Buffer I/O, Buffer Latch, CPU, Lock, Memory, Network I/O etc.
Data File I/O: – displays information related to Disk File I/O activities for relational databases within the SQL Server Instance. The information related to all the system and user databases are available. This section enables quick identification if a particular database is being heavily hit with Disk I/O. This section also provides the option to sort and filter each column. The information is displayed for each database, database files, MB/Sec Read, MB/Sec Written and Response Time (ms).
Recent Expensive Queries: – helps Database Administrators to identify queries which are poorly performing and impacting performance of the SQL Server Instance. SQL Server at a given time will display 10–15 worst and most expensive queries executing on the server. There is the option to sort and filter each column. A notable feature, is once the problematic query has been identified, right click the same and choose the “Edit Query Text” option to edit the query and see the current execution plan used by the slow performing query, this can be done by choosing the “Show Execution Plan” option. The other important columns which need require observation while identifying problematic queries are Executions/min, CPU (ms/sec), physical Reads/sec, Logical Reads/sec, Average Duration (ms) and Plan count.
Intellisense Support
One of the most requested features by database developers and administrators in SQL Server was Intellisense Support, Microsoft released Intellisense Support in SQL Server 2008. Intellisense is a feature that reads the internal metadata and provides users with a list of available objects and properties while the code is being written. Prior to SQL Server 2008 this feature was supported only by third party products. The Intellisense feature in SQL Server 2008 helps database developers to write the TSQL code effectively, especially those not familiar with the syntax.
In the below snippet, SQL Server is helping the developer select the objects which are owned by Person Schema of AdventureWorks database.
Database Developers or Administrators can enable or disable this feature either from Menu; Query | Intellisense Enabled or enable or disable this feature by clicking Intellisense Enabled button from the SQL Editor Toolbar.
If working a lot with SQL Server 2000 or 2005 then, a third party tool such as SQL Prompt from Red-Gate Software needs to be utilised to use the Intellisense feature.
Region Outlining
The Region Outlining feature groups certain set of TSQL code; thereby allowing developers to collapse and expand the TSQL code. This feature helps developers navigate within a very large TSQL code. A region is automatically created by the SQL Query Editor for a batch, BEGIN END blocks, BEGIN TRY END TRY block, BEGIN CATCH END CATCH blocks etc. The below snippet is an example of region outlining feature which is available in SQL Server 2008 Management Studio.
Conclusion
This article demonstrated how database developers and database administrators can benefit from new features like Activity Monitor, Intellisense Support and Region Outlining which is available in SQL Server 2008 Management Studio. The next article will contain more new interesting features and improvement that are available within SQL Server Management Studio of SQL Server 2008.