Using SQL Server Management Studio Tools

Using Object Explorer

The Object Explorer window that existed in the SQL Server 2000 Query Analyzer has been integrated into SSMS. It has the same tree-like structure that was present in SQL Server 2000 but contains some significant improvements over its predecessor. The most significant feature for those folks managing a large number of database objects is the ability to populate the Object Explorer tree asynchronously. This may not hit home for folks who deal with smaller databases, but those who waited on the synchronous population of Object Explorer in SQL Server 2000 will be excited. The Object Explorer tree in SSMS displays immediately and allows navigation in the tree and elsewhere in SSMS while the population of the tree is taking place.

The Object Explorer is adaptive to the type of server that it is connected to. For a database engine server, the databases and objects such as tables, stored procedures, and so on are displayed in the tree. If you connect to an Integration Services server, the tree displays information about the packages that have been defined on that type of server. Figure 3.7 shows an example of the Object Explorer with several different types of SQL Server servers displayed in the tree. Each server node has a unique icon that precedes the server name, and the type of server is also displayed in parentheses following the server name.

Figure 3.7.  Multiple server types in Object Explorer.

The objects displayed in the Object Explorer tree can be filtered in SQL Server 2005. The number of filters is limited, but those that are available can be helpful. For example, you can filter the tables that are displayed in Object Explorer based on the name of the table, the schema that it belongs to, or the date on which it was created. Again, for those who deal with large databases and thousands of database objects, this feature is very helpful.

Administrators also find the enhanced scripting capabilities in the Object Explorer very useful. The scripting enhancements are centered mostly on the administrative dialog boxes. These dialogs now include a script button that allows you to see what SSMS is doing behind the scenes to effect your changes. In the past, the Profiler could be used to gather this information, but it was more time-consuming and less integrated than what is available now.

Figure 3.8 shows an example of an administrative dialog, with the scripting options selected at the top. You can script the commands to a new query window, a file, the Windows Clipboard, or a job that can be schedule to run at a later time.

Aside from these features, many of the features and much of the functionality associated with the Object Explorer is similar to what was found in SQL Server 2000. Keep in mind that there are some additional nodes in the Object Explorer tree and that some of the objects are located in different places. There is now a separate node for the SQL Server Agent that contains scheduled jobs and related objects. Linked servers are now located under the Server Objects node, and several new additions are available in the Management node that were found elsewhere in the prior version.

Figure 3.8.  Scripting from administrative dialogs.

Using Activity Monitor

The functionality that was previously available in the Current Activity node of Enterprise Manager has been ported to a new tool named the Activity Monitor. The Activity Monitor is a non-modal window that is launched from the Object Explorer tree. To access it, you open the Management node of the tree and double-click the Activity Monitor node. Figure 3.9 shows an example of the Activity Monitor window.

The default Process Info page lists current processes on the database server. By default, the system processes are not listed, but you can add them to the display by using the filtering capabilities in the application. If you click the Filter button, a Filter Settings window like the one shown in Figure 3.10 is displayed. You can set the Show System Processes value to True to display all the processes, and you can adjust any of the other filter values to display the desired set of processes.

The other two pages in the Activity Monitor display information about locks on the server. You can display locks by server process ID (SPID) or select locks based on a specific database object. This information is similar to what is retrieved when you run the sp_lock system stored procedure or the sys.dm_tran_locks dynamic management view.

Figure 3.9.  Process info in the Activity Monitor.

Figure 3.10.  Filter settings in the Activity Monitor.

NOTE

Each of the pages in the Activity Monitor has an abundance of columns that display useful information. The default window is not nearly big enough to display all the information. You can use the scrollbar to navigate to the columns that are hidden. To rearrange the order of the columns, you simply drag a column header to the desired location.

One of the most impressive features of the Activity Monitor is its ability to refresh the display automatically. You can click the View Refresh settings option on the left side of the screen to adjust the refresh rate. You can select the Auto Refresh Every option and select the number of seconds between refreshes to have the screen automatically refresh.

Using Log File Viewer

The Log File Viewer is another non-modal window that is new to SQL Server 2005. Like the Activity Monitor, it houses information that was previously displayed in the document window in the SQL Server 2000 Enterprise Manager. It can display log files that are generated from several different sources, including Database Mail, SQL Server Agent, SQL Server, and Windows NT. 

The Log File Viewer can be launched from the related node in the SSMS Object Explorer. For example, you can select the Management node and expand
SQL Server Error Logs. If you double-click one of the error logs listed, a new Log File Viewer window is launched, displaying the SQL Server log file entries for the log type selected (see Figure 3.11).

Figure 3.11.  SQL Server logs displayed in the Log File Viewer

NOTE

By default, entries are shown in the SQL Server Log File Viewer from newest to oldest. This is different from the default order in the SQL Server 2000 Enterprise Manager, which displayed the log file entries from oldest to newest.

One of the first things you notice when you launch the Log File Viewer is that there is a tree structure at the top-left corner of the screen that shows the log files you are viewing. You can see that there are four different log types available: Database Mail, SQL Agent, SQL Server, and Windows NT. You can choose to display multiple log files within a given log type (for example, the current SQL Server log and Archive #1) or you can select logs from different sources. For example, you can display all the current log entries for SQL Server and the current log entry for the SQL Server Agent.

When multiple logs are selected, you can differentiate between the rows shown on the right side of the Log File Viewer by looking at the Log Source column and the Log Type column. The Log Source values match up with the names that are shown in the tree structure where the log was selected. The Log Type column shows the type of log, such as SQL Agent or SQL Server. Rows from the different log types are displayed together and sorted according to the date on which the row was created. The sort order cannot be changed.

TIP

You can rearrange the order of the columns shown in the Log File Viewer. You simply click the column header and drag the column to the desired location. When viewing rows for more than one log type or multiple logs, it is best to drag the Log Type and Log Source columns to a location that is easily viewed so that you can distinguish between the entries.

Other noteworthy features in the Log File Viewer include the ability to filter and load a log from an external source. You can filter on dates, users, computers, the message text, and the source of the message. You can import log files from other machines into the view by using the Load Log facility. This works hand-in-hand with the Export option, which allows you to export the log to a file. These files can be easily shared so that others can review the files in their own Log File Viewer.

Development Tools

SSMS delivers an equally impressive number of enhancements for database developers. These new tools are based on tools such as Query Analyzer that were available in prior versions of SQL Server. They deliver the same functional value available in prior releases and offer enhancements that address some of the shortcomings. 

The Query Editor

The Query Editor sits at the top of the list for new development tools in SSMS. The Query Editor, as its name indicates, is the editing tool for writing queries in SSMS. It contains much of the functionality that was contained in SQL Server 2000’s Query Analyzer. The ability to write Transact-SQL (T-SQL) queries, execute them, return results, generate execution plans, and many of the other features you may be familiar with in Query Analyzer are also available with the Query Editor.

One main difference with the Query Editor is that is has been integrated into the SSMS environment. In SQL Server 2000, the Query Analyzer was a separate application with its own independent interface. In SQL Server 2005, SSMS houses the query-editing capabilities along with all the administrative capabilities.

NOTE

The biggest upside to the integration of the query-editing tool into the SSMS environment is that you can find almost anything you need to administer or develop on your SQL Server database in one spot. There is no need to jump back and forth between applications. One possible downside, however, is that SSMS may be much more than some database developers need.

Clicking the New Query button, opening a file, and selecting the Script to File option from a list of database objects in the Object Explorer are just a few of the ways to launch the Query Editor. Figure 3.12 shows the Query Editor window with a sample SELECT statement from the AdventureWorks database. The figure shows the Query Editor window displayed on the right side of the screen and the Object Explorer on the left side.

Figure 3.12.  The Query Editor window in SSMS.

The basic editing environment within the Query Editor is similar to Query Analyzer. The top portion of the Query Editor window contains the query. The bottom portion contains the results of an executed query. The results can be displayed as text, displayed in a grid format, or output as XML. However, in the Query Editor, windows are by default managed differently than with Query Analyzer. Multiple Query Editor windows are displayed in a tabbed format; in comparison, Query Analyzer displayed a separate window for each query.

TIP

The tabbed document display has some advantages, but you can set an option in SSMS that causes the Query Editor to behave much like the Query Analyzer. To do this, you select Tools, Options to launch the Options dialog. The default page has a section named Environmental Layout. If you choose the MDI Environment option, you set SSMS in MDI mode instead of the tabbed layout.

Please check back tomorrow for the continuation of this article.