SQL Server Management Studio (SSMS)

Online learning

​fREE

SQL DBA SCHOOL

Founder Abubeker Refaw

 SQL Server Management Studio (SSMS) is an integrated application that provides access to most of the graphical tools you can use to perform administrative and development tasks on SQL Server 2008. SSMS was introduced with SQL Server 2005 and replaced the Enterprise Manager, Query Analyzer, and Analysis Manager that were available in SQL Server 2000. Microsoft consolidated all those tools into one, with a focus on providing a tool that suits the needs of both developers and database administrators (DBAs). SSMS is a complicated tool that provides an entry point to almost all of SQL Server’s functionality. The functionality that is accessible from SSMS is entirely too much to cover in one chapter. The aim of this chapter is to give a basic overview of SSMS while touching on the features that are new to SQL Server 2008. Others chapters in this book discuss the components of SSMS and provide more detailed coverage.

What’s New in SSMS
SSMS is loaded with new features in SQL Server 2008. This tool was introduced in SQL Server 2005, and it brought quite a bit of change with it. There is also quite a bit of change with SQL Server 2008, but the basic look-and-feel of the application remains much the same as it was in SQL Server 2005. The standout features in the SQL Server 2008 SSMS include four new features geared toward the administrator and three new features geared toward the developer. Of course, all these features could be used by both administrators and developers.

The new administrator features in SSMS include a beefed-up Activity Monitor, a new Object Search option, a customizable Object Explorer Details window, and a new management tool named SQL Server Utility that was added with SQL Server 2008 R2. The Activity Monitor now contains four separate graphs that look like graphs displayed in Task Manager, and they pull information similar to what you might see in System Monitor. The Object Search option allows you to search for database objects by name while changes to the Object Explorer Details window significantly expand the amount of available information and allow the user to change the information that is displayed.

Finally, the new SQL Server Utility allows for the capture of resource information across multiple servers and provides one unified view for the display of this information. Enhancements that are focused on the developer include IntelliSense in the Query Editor, an integrated Transact-SQL (T-SQL) Debugger, and a Multiserver Query execution option. IntelliSense helps with the completion of T-SQL code as you write it. This feature was much anticipated for SQL Server 2005, but it never made it to the released code. That anticipation continued with SQL Server 2008, and fortunately, Microsoft has delivered. This feature should not disappoint, and it will ultimately improve your productivity. A debugging tool that is integrated into SSMS is another great new feature. Those who have developed stored procedures with thousands of lines of code will particularly appreciate this new feature. You can set breakpoints, evaluate variables, and step through the code line by line. This debugging capability applies to all T-SQL in the query editor window. Multiserver queries is the last standout feature offered with SQL Server 2008. This feature allows you to run a single query against more than one server. The results returned from each server are displayed in a single result window. If you’re managing many servers, this feature can be a real timesaver. This page further explores the new features in SSMS. It first examines the features at the environmental level, focusing on how SSMS behaves and how to best utilize the environment. Next, it looks at the administrative tools and what changes have been made to help you better manage your SQL Server environment. Finally, this page looks at the development tools available with SSMS and changes made to improve your SQL Server development experience.

                                                                                 The Integrated Environment
If you have been working with SQL Server for a long time, you may remember the SQL Enterprise Manager that came with SQL Server 6.5. In some respects, with SSMS, Microsoft has moved back to the paradigm that existed then. Like the SQL Server 6.5 Enterprise Manager, SSMS provides an integrated environment where developers and DBAs alike can perform the database tasks they need. Say goodbye to Query Analyzer, Analysis Manager, and a number of other disparate tools used in SQL Server 2000 and say hello to SSMS, which provides “one-stop shopping” for most of your database needs.

                                                                                   Administration Tools
The tools available with SSMS can be broadly categorized into tools that are used for administering SQL Server and tools that are used for developing or authoring new SQL Server objects. As a matter of practice, developers use some of the administrative tools, and administrators use some of the development tools. SSMS comes with an expanded set of tools to help with SQL Server administrative tasks. It builds on the functionality that was available in SQL Server 2005 and adds some new tools and functionality to help ease the administrative burden.

Registered Servers

Registered serversis a concept in SQL Server 2008 that represents a division between managing servers and registering servers. With the SQL Server 2000 Enterprise Manager, the Microsoft Management Console (MMC) tree was displayed on the left side of the Enterprise Manager screen, and it contained servers that had been registered via that tree. Any registered servers or groups were listed in the tree, along with any of the associated objects. Registered servers are managed and displayed in the Registered Servers component window. Figure 4.5 shows an example of the Registered Servers window, with several server groups and their associated registered servers. You can add new groups or servers any time so that you have a handy way of organizing the servers you work with.

Benefits of Registered Servers

With Registered Servers you can:

• Register servers to preserve the connection information.
  
• Determine if a registered server is running.
  
• Easily connect Object Explorer and Query Editor to a registered server.
  
• Edit or delete the registration information for a registered server.
  
• Create groups of servers.
  
• Provide user-friendly names for registered servers by providing a value in the Registered server name box that is different from the Server name list.
  
• Provide detailed descriptions for registered servers.
  
• Provide detailed descriptions of registered server groups.
  
• Export registered server groups.
  
• Import registered server groups.
  
• View the SQL Server log files for online or offline instances of SQL Server.

The servers listed above are all Database Engine servers. These server types are the conventional SQL Server instances, like those you could register in the SQL Server 2000 Enterprise Manager. You can also register several other types of servers. The icons across the top of the Registered Servers window indicate the types of servers that can be registered. In addition to Database Engine servers, you can also register servers for Analysis Services, Reporting Services, SQL Server Mobile, and Integration Services. The Registered Servers window gives you one consolidated location to register all the different types of servers available in SQL Server 2008. You simply click the icon associated with the appropriate server type, and the registered servers of that type are displayed in the Registered Servers tree.

The SQL Server 2008 Registered Servers window enables you to register servers that are running SQL Server 2005, SQL Server 2000, and SQL Server 7.0. You can manage all the features of SQL Server 2005 and SQL Server 2000 with SQL Server 2008 tools. You can also have both sets of tools on one machine. The SQL Server 2000, SQL Server 2005, and SQL Server 2008 tools are compatible and function normally together. Management tools from prior SQL Server versions cannot be used to manage SQL Server 2008 instances. For example, the SQL Server 2000 Enterprise Manager cannot be used to manage SQL Server 2008. You can connect the Query Analyzer to a SQL Server 2008 instance and run queries, but the Object Explorer and other tools are not compatible with SQL Server 2008.

When a server is registered, you have several options available for managing the server. You can right-click the server in the Registered Servers window to start or stop the related server, open a new Object Explorer window for the server, connect to a new query window, or export the registered servers to an XML file so that they can be imported on another machine.

The import/export feature can be a real timesaver, especially in environments where many SQL servers are managed. You can export all the servers and groups registered on one machine and save the time of registering them all on another machine. For example, you can right-click the Database Engine node, select Export, and then choose a location to store the XML output file. Then all you need to do to register all the servers and groups on another machine is move the file to that machine and import the file.

Object Explorer

Object Explorer provides a hierarchical user interface to view and manage the objects in each instance of SQL Server. The Object Explorer Details pane presents a tabular view of instance objects, and the capability to search for specific objects. The capabilities of Object Explorer vary slightly depending on the type of server, but generally include the development features for databases, and management features for all server types.

The Object Explorer window that existed in the SQL Server 2000 Query Analyzer was coordinated into SSMS in SQL Server 2005. SQL Server 2008 keeps on utilizing a coordinated Object Explorer that acts like SQL Server 2005.. The most huge element for those people dealing with countless articles is the ability to populate the Object Explorer tree nonconcurrently. This may not hit home for people who manage littler databases, however it can be a continuous saver for those that are managing numerous databases on a solitary SQL Server case or for those that work with databases that have a critical number of database items. The Object Explorer tree in SSMS shows quickly and permits route in the tree and somewhere else in SSMS while the number of inhabitants in the tree is occurring. The Object Explorer is versatile to the sort of server it is associated with. For a Database Engine server, the databases and questions, for example, tables, put away systems, thus on are shown in the tree. On the off chance that you associate with an Integration Services server, the tree shows data about the bundles characterized on that kind of server. Figure 4.6 demonstrates a sample of the Object Explorer with a few distinct sorts of SQL Server servers showed in the tree. Every server hub has an one of a kind symbol that goes before the server name, and the sort of server is additionally shown in enclosures taking after the server name.

One often-overlooked Object Explorer feature is the reports option that was added in SQL Server 2005 and still exists in SQL Server 2008. This option is available by right-clicking on a node in the Object Explorer. Reports are not available for every node in the Object Explorer tree, but many of them do have this option. Most reports are found in the toplevel nodes in the tree. For example, if you right-click on a database in the Object Explorer tree and then select Reports and Standard Reports, you see more than a dozen available reports. These reports include Disk Usage, Backup and Restore Events, Top Transactions by Age, and a host of others. Graphs are included with some reports, and you can export or print all these reports. The next picture shows an example of the Disk Usage reportfor the AdventureWorks2008 database.

The graphs are easy to read, and a few areas of the report can be extended to give more detail. Shots at the base of a report are hubs that can be extended. For instance, the Disk Space Used by Data Files at the base of above picture can be extended to show insights about each of the information documents.

Activity Monitor

The Activity Monitor has seen some dramatic changes in SQL Server 2008. These changes build on the foundation established in SQL Server 2005 and help provide much more information related to the performance of your SQL Server instance. Before we get into the details of the Activity Monitor, let’s make sure you know where to find it. It is no longer found in the Management node of the Object Explorer. Instead, you right-click on the name of the server instance in the Object Explorer, and you see a selection for Activity Monitor.

Activity Monitor is a tabbed document window that has the following expandable and collapsible panes: Overview, Active User Tasks, Resource Waits, Data File I/O, and Recent Expensive Queries. When any pane is expanded, Activity Monitor is querying the instance for information. When a pane is collapsed, all querying activity stops for that pane. You can also expand one or more panes at the same time to view different kinds of activity on the instance.

When the Activity Monitor launches, you see a new display with four different graphs, as shown in the below picture. The graphs include % Processor Time (from SQL Server), Waiting Tasks, Database I/O and Batch Requests. These graphs give you a quick performance snapshot for your SQL Server in one spot without having to launch System Monitor or some other monitoring tool to view this kind of information. You also find more detailed performance information below the graphs. This information is grouped into four categories: Processes, Resource Waits, Data File I/O and Recent Expensive Queries. Clicking on the expand button for one of these categories presents the details you are looking for. These details contain drop-down headings that allow you to filter the results and view only the information you need.

The Processes Details window contains information similar to what was displayed in the SQL Server 2005 Activity Monitor. These details include information similar to what is returned with the sp_who system stored procedure. The server process ID (SPID) is listed in a column named Session ID, and the related information for each SPID is displayed in the remaining columns. If you right-click on a particular process, you can see the details of that process. You can then kill that process or launch the SQL Server Profiler to trace the activity for the process. the bottom picture  shows an example of the expanded Processes details window. The Resource Waits window (that is displayed below the Process window) can help you identify bottlenecks on your server. It details the processes waiting for other resources on the server. The amount of time a process is waiting and the wait category (what the process is waiting for) are found in this display. If you click on the Cumulative Wait Time column, the rows are sorted by this column and you can find the wait category that has been waiting the longest. This sorting capability applies to all the columns in the display

The Data File I/O window lists each database and its related database files. The amount of disk I/O experienced by each of the files is detailed in the columns of this display. You can isolate the database and files that are most heavily hit with read or write activity as well as the databases that may be suffering from poor I/O response with this screen. Finally, the Recent Expensive Queries window displays information similar to what you can obtain using catalog views. It provides statistics for all the databases on the instance and is a quick and easy way to find and tune expensive SQL statements. If you right-click on a row in the display and click Edit Query Text, you can see the entire SQL text associated with the query. You are able to click on one of the column headings such as CPU to sort the display according to the metric you feel defines cost. Best of all, you can rightclick on a row and choose Show Execution Plan, and you have the Query Plan ready for analysis

Log File Viewer

The Log File Viewer is another nonmodal window that is essential for administering your SQL Server. 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 View  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 the bottom picture)

One of the first things you notice when you launch the Log File Viewer is that a tree structure at the top-left corner of the screen 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 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.

Other noteworthy features in the Log File Viewer include the capability 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 facility 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.

​

SQL Server Utility

The SQL Server Utility was added in SQL Server 2008 R2 and is geared toward multiserver management. It provides several new hooks in the SSMS environment that improve visibility and control across multiple SQL Server environments. Access to these new hooks is provided through a new Utility Explorer that can be displayed within your SSMS environment. This Utility Explorer has a tree-like structure similar to the Object Explorer, and it provides rich content related to the health and integrity of the SQL Server environments you have selected to manage using the SQL Server Utility. The below  Figue shows an example of the type of information the Utility Explorer can display.

The SQL Server Utility provides a holistic view of the health and utilization of the resources associated with managed instances of SQL Server and registered database applications. Here are the components and terms affiliated with it:

• Utility control point (UCP). The UCP is configured on a SQL Server instance. It provides the foundation for the SQL Server Utility, using SQL Server Management Studio (SSMS) to monitor SQL Server resource health and utilization. The UCP supports a number of actions, including specifying resource utilization policies that track an organization’s utilization requirements.
• Utility Explorer. Accessed from SSMS, this interface lets you manage and control the SQL Server Utility. You can use Utility Explorer to connect to a utility, create a UCP, enroll the SQL Server instances to be managed, manage utilities, view dashboard and drilldown views affiliated with managed instances of SQL Server, and more.
  
• Utility management data warehouse (UMDW). This relational database stores data collected by managed instances of SQL Server. The UMDW database (sysutility_mdw) is automatically created on a SQL Server instance when the UCP is created. It utilizes the simple recovery model and requires approximately 2GB of storage for every managed instance of SQL Server per year.
  
• Utility Information data collection set. When an instance is managed by the UCP, the Utility Information data collection set is installed and automatically started. It collects data and forwards it to the UMDW.
  
• Data-tier application (DAC). A DAC is a single unit for developing, deploying, and managing data-tier objects. A DAC package includes database application schema, tables, views, stored procedures, and logins. A DAC can be generated with the new SQL Server Data-tier Application project template in Visual Studio 2010 or by reverse engineering an existing database.
  

Development Tools
SSMS delivers an equally impressive number of features for database developers. Many of the features were available with SQL Server 2005, but SQL Server 2008 has added some new ones as well. T-SQL Debugging, IntelliSense in the Query Editor, and multiserver queries are a few of those new tools for developers found in SQL Server 2008. These new tools and the other essential developer tools from SSMS are discussed in the following sections.

The Query Editor

The Query Editor sits at the top of the list for 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 capability to write T-SQL queries, execute them, return results, generate execution plans, and use 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 2008, SSMS houses the query-editing capabilities along with all the administrative capabilities.

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. The below picture shows the query editor window with a sample SELECT statement from the AdventureWorks2008 database. The query editor window is displayed on the right side of the screen and the Object Explorer on the left side.

IntelliSense

IntelliSense has finally made it to the SQL Server Query Editor. This much-anticipated tool was slated for SQL Server 2005, but it was pulled before making it to the marketplace. Fortunately, it made it to SQL Server 2008, and it was worth the wait. This is especially true for those developers who have been working with Visual Studio or other Microsoft development tools that have this feature. IntelliSense is a handy tool that helps you complete queries as you are typing them in the query editor window. Start typing and you will see. For example, type SELECT * FROM A in the query editor window, and a drop-down appears in the query editor window after you start typing the first letter after the FROM clause. The drop-down, in this case, contains the databases and tables from which you can select data. If you type in a stored procedure name to execute, a drop-down shows you the parameters that the stored procedure accepts. Type SYS. in the query editor window, and you see a drop-down of all the objects available in the SYS schema. This includes catalog views and the related columns that these views contain. If you type in a query that is incorrect, IntelliSense places a red squiggly line under the part of the query that is syntactically incorrect. The value of this tool will become more apparent as you use it. It can be confusing at times, but it will ultimately speed up your development time. It can also reduce the number of times you need to go to Books Online or some other help source and will make your development life easier.

Query Editor Types

The Query Editor in SQL Server 2008 enables you to develop different types of queries. You are not limited to database queries based on SQL. You can use the Query Editor to develop all types of SQL Server Scripts, including those for SQL Server Analysis Services (SSAS) and SQL Server Mobile Edition. The SSAS queries come in three different flavors: multidimensional expressions (MDX), data mining expressions (DMX), and XML for analysis (XMLA). Only one selection exists for creating SQL Server Mobile Edition scripts.
 

Disconnected Editing

SQL Server 2008 is able to use the code editor without a database connection. When creating a new query, you can choose to connect to a database or select Cancel to leave the code pane disconnected. To connect to the database later, you can right-click in the code pane window and select the Connect option. You can also disconnect the Query Editor at any time or choose the Change Connection option to disconnect and connect to another database all at once. Along with disconnected editing are some changes to the Windows behavior that are worth noting.

The biggest changes relate to the behavior of query windows currently open at the time a file is opened for editing. With SQL Server 2000 Query Analyzer, the currently selected window would be populated with the contents of the file you were opening. Prior to this replacement, a prompt would be displayed asking whether you wanted to save your results. If the query window was empty, the contents would be replaced without the prompt for saving. With SQL Server 2008, a new query window is opened every time a new file is opened. The new window approach is faster but can lead to many more open windows in the document window. You need to be careful about the number of windows/connections you have open. Also, you need to be aware that the tabbed display shows only a limited number of windows. Additional connections can exist even if their tabs are not in the active portion of the document window.

Editing sqlcmd Scripts in SSMS

sqlcmd is a command-line utility introduced in SQL Server 2008. You can use it for ad hoc interactive execution of T-SQL statements and scripts. It is basically a replacement for the ISQL and OSQL commands used in versions prior to SQL Server 2005. (OSQL still works with SQL Server 2008, but ISQL has been discontinued.) You can write, edit, and execute sqlcmd scripts within the Query Editor environment. The Query Editor in SSMS treats sqlcmd scripts in much the same way as other scripts. The script is color-coded and can be parsed or executed. This is possible only if you place the Query Editor in SQLCMD mode, which you do by selecting Query, SQLCMD Mode or selecting the SQLCMD mode icon from the SSMS toolbar. A sample sqlcmd script in SSMS that can be used to back up a database. This example illustrates the power and diversity of a sqlcmd script that utilizes both T-SQL and sqlcmd statements. It uses environment variables set within the script. The script variables DBNAME and BACKUPPATH are defined at the top of the script with the SETVAR command. The BACKUP statement at the bottom of the script references these variables, using the convention $(variablename), which substitutes the value in the command.
 

SQL Server Management Studio includes the following general features:

• Supports most administrative tasks for SQL Server.
  
• A single, integrated environment for SQL Server Database Engine management and authoring.
  
• Dialogs for managing objects in the SQL Server Database Engine, Analysis Services, and Reporting Services, that allows you to execute your actions immediately, send them to a Code Editor, or script them for later execution.
  
• Non-modal and resizable dialogs allow access to multiple tools while a dialog is open.
  
• A common scheduling dialog that allows you toperformaction of the management dialogs at a later time.
  
• Exporting and importing SQL Server Management Studio server registration from one Management Studio environment to another.
  
• Save or print XML Showplan or Deadlock files generated by SQL Server Profiler, review them later, or send them to administrators for analysis.
  
• A new error and informational message box that presents much more information, allows you to send Microsoft a comment about the messages, allows you to copy messages to the clipboard, and allows you to easily e-mail the messages to your support team.
  
• An integrated Web browser for quick browsing of MSDN or online help.
  
• Integration of Help from online communities.
  
• A tutorial on SQL Server Management Studio to help you take advantage of the many new features and become more productive right away.
  
• A newactivitymonitor with filtering and automatic refresh.
  
• Integrated Database Mail interfaces.

Using the Query Designer in the Query Editor

A graphical query design tool is accessible from the query editor window where you write your queries. This is a great option that was missing in SQL Server 2000. With SQL Server 2000, you could access a graphical query designer by opening a table in Enterprise Manager and selecting Query, but this option was disconnected from the Query Analyzer environment, where the queries were authored. This tool was introduced in SQL Server 2005 and remains generally unchanged in SQL Server 2008. With SQL Server 2008, you can right-click in the query editor window and choose Design Query in Editor. A dialog box appears, allowing you to add tables to the graphical query designer surface. The selected tables are shown in a window that allows you to select the columns you want to retrieve. Selected columns appear in a SELECT statement displayed at the bottom of the Query Designer window. The below pic shows an example of the Query Designer window that contains two tables from the AdventureWorks2008 database. The two tables selected in this figure are related, as indicated by the line between them.
 

The T-SQL statements are generated automatically as you select various options on the Query Designer screen. If you select Sort Type, an ORDER BY clause is added. If you choose an alias for a column, it is reflected in the T-SQL. If tables are related, the appropriate joins are generated. When you click OK on the Query Designer window, the related T-SQL is automatically placed in the query editor window. You can edit the T-SQL as needed or use it as is. You can imagine the time savings you can achieve by using this tool.

T-SQL Debugging

Finally, you are able to debug T-SQL from within the SQL Server development environment. Yes, you could do this kind of thing using Visual Studio, but database developers should be able to debug in the environment where they generally develop their SQL statements—within SSMS. SQL Server 2008 provides this capability, and it works well. The trickiest part of debugging may be starting the debugger. It is not all that difficult but may be less than obvious for some. For example, let’s say you want to debug a stored procedure. To do this, you right-click on the stored procedure in the Object Explorer and select Script Stored Procedure As, Execute To, New Query Editor Window, and a script for executing the procedure is generated. If the stored procedure has parameters, you add the SQL to assign a value to those parameters to the script. Now you are ready to debug this script and the related stored procedure.

Multiserver Queries

Another slick new option available with SQL Server 2008 is the capability to execute a script on multiple servers at once. Multiserver queries allow the contents of a single query editor window to be run against all the servers defined in a given registered server group. After the group is created and servers are registered in the group, you can right-click on the group and select the New Query option to create a query window that can be run against all the servers in the group.