• Article

Overview of SQL Server Utility

SQL Server customers have a requirement to manage their SQL Server environment as a whole, addressed in this release through the concept of application and multiserver management in the SQL Server Utility.

The SQL Server Utility models an organization’s SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). Entities that can be viewed in the SQL Server UCP include:

  • Instances of SQL Server

  • Data-tier applications

  • Database files

  • Storage volumes

Resource utilization dimensions that can be viewed in the SQL Server UCP include:

  • CPU utilization

  • Storage space utilization

For more information, see the following sections in this topic:

  • Getting Started with SQL Server Utility

  • Utility Control Point Overview

  • SQL Server Utility Requirements

SQL Server Utility Architecture

Figure 1. SQL Server Utility architecture in SQL Server 2008 R2.

Getting Started with SQL Server Utility

The typical user scenario begins with creation of a utility control point which establishes the central reasoning point for the SQL Server Utility. The UCP provides a consolidated view of resource health collected from managed instances of SQL Server in the SQL Server Utility. After the UCP is created, you enroll instances of SQL Server into the SQL Server Utility so that they can be managed by the UCP.

Each managed instance of SQL Server is instrumented with a data collection set that sends configuration and performance data to the UCP every 15 minutes. Data-tier applications automatically become managed by the SQL Server Utility if they belong to a managed instance of SQL Server. Other monitoring parameters are database file space utilization, CPU utilization, and storage volume utilization.

Summary and detailed data is presented in Management Studio for each instance of SQL Server and data-tier application managed by the SQL Server Utility. The SQL Server Utility dashboard in SSMS presents an at-a-glance summary of performance and configuration data for managed instance and data-tier application CPU utilization, database file utilization, storage volume utilization, and computer CPU utilization. Data displays provide separation of overutilized and underutilized resources, as well as graphs of CPU utilization and storage utilization over time.

The SQL Server Utility viewpoints and details in SSMS present detailed performance and configuration status for each instance of SQL Server and data-tier application managed by the SQL Server Utility, and access to policy controls. The list view displays current status compared to policy definitions, and policy controls display policy definitions for the following utilization categories:

  • Instance CPU utilization policies for instances of SQL Server and for data-tier applications

  • File space utilization policies for database files and log files

  • Computer CPU utilization policies for host resources

  • Storage volume utilization policies for host resources

Each instance of SQL Server and data-tier application managed by the SQL Server Utility can be monitored based on global policy definitions or based on individual policy definitions.

The combination of summary and detailed data presented in the UCP for both underutilization and overutilization policies, and for a variety of key parameters, enables resource consolidation opportunities and resource overutilization to be identified with ease. Health policies are configurable, and can be adjusted to change either upper or lower resource utilization thresholds. You can change global monitoring policies, or configure individual monitoring policies for each entity managed in the SQL Server Utility.

Use the following steps to get started with SQL Server Utility:

To get started with SQL Server Utility, you must create a utility control point and enroll instances of SQL Server. To access SQL Server Utility configuration steps and video documentation through Management Studio, select View on the SSMS menu bar, then select Utility Explorer. A Getting Started page will be on one of two tabs that are displayed in the SSMS content pane.

To launch the wizard for a SQL Server Utility operation, click on the title of the operation; for example, "Create a Utility Control Point (UCP)." To view a video of the operation, click on the Video button to the left of the title.

These are one-time operations that create the SQL Server Utility.

  1. Create a utility control point - How to: Create a SQL Server Utility Control Point (SQL Server Utility)

  2. Enroll instances of SQL Server into the SQL Server Utility - How to: Enroll an Instance of SQL Server (SQL Server Utility)

After your SQL Server Utility is created, use SSMS to connect to the SQL Server Utility, monitor managed instances of SQL Server, and customize monitoring policies to meet the needs of your organization.

  1. Use SSMS to connect to the SQL Server Utility - Connecting to a SQL Server Utility

  2. Monitor managed instances of SQL Server - How to: View Resource Health Policy Results (SQL Server Utility)

  3. Customize SQL Server Utility monitoring policies to meet your needs - How to: Modify a Resource Health Policy Definition (SQL Server Utility)

Utility Control Point Overview

The SQL Server Utility is managed through a utility control point using Utility Explorer in SSMS. The UCP is configured on an instance of SQL Server. It provides the central reasoning point for the SQL Server Utility using SSMS to organize and monitor SQL Server resource health. The UCP supports actions like specifying resource utilization policies that track the utilization requirements of your organization.

The Utility Explorer user interface in SSMS provides a hierarchical tree view of managed entities in the Utility. Utility Explorer is similar to the SSMS Object Explorer for navigating through and managing resources in the SQL Server Utility. The dashboard and list views provide summary and detailed presentation of resource health and configuration details for managed instances of SQL Server, data-tier applications, and host resources like CPU utilization, file space utilization, and volume space utilization.

The UCP collects configuration and performance information from managed instances of SQL Server every 15 minutes. This information is stored in the utility management data warehouse (UMDW) on the UCP; the UMDW file name is sysutility_mdw. SQL Server performance data is compared to policies to help identify resource use bottlenecks and consolidation opportunities.

SQL Server Utility Requirements

In this release, the UCP and all managed instances of SQL Server must satisfy the following requirements:

  • SQL Server must be version 10.50 or higher.

  • The SQL Server instance type must be Database Engine.

  • The SQL Server Utility must operate within a single Windows domain, or across domains with two-way trust relationships.

  • The SQL Server service accounts on the UCP and all managed instances of SQL Server must have read permission to Users in Active Directory.

In this release, the UCP must satisfy the following requirements:

  • The SQL Server edition must be Datacenter, Enterprise, Developer, or Evaluation.

  • We recommend that the UCP is hosted by a case-sensitive instance of SQL Server.

  • Consider the following recommendations for capacity planning on the UCP computer:

    • In a typical scenario, disk space used by the UMDW database (sysutility_mdw) on the UCP is approximately 2 GB per managed instance of SQL Server per year. This estimate can vary depending on the number of database and system objects collected by the managed instance. The UMDW (sysutility_mdw) disk space growth rate is highest during the first two days.

    • In a typical scenario, disk space used by msdb on the UCP is approximately 20 MB per managed instance of SQL Server. Note that this estimate can vary depending on the resource utilization policies and the number of database and system objects collected by the managed instance. In general, disk space usage increases as the number of policy violations increases and the duration of the moving time window for volatile resources increases.

    • Note that removing a managed instance from the UCP will not reduce the disk space used by UCP databases until expiration of data retention periods for the managed instance.

In this release, all managed instances of SQL Server must satisfy the following requirements:

  • We recommend that if the UCP is hosted by a case-insensitive instance of SQL Server, then managed instances of SQL Server should also be case-insensitive.

  • FILESTREAM data is not supported for SQL Server Utility monitoring.

For more information, see Maximum Capacity Specifications for SQL Server.