Chapter 5: Consolidation and Monitoring in SQL Server

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Today's competitive economy dictates that organizations reduce cost and improve agility in their database environments. This means the large percentage of organizations out there running underutilized Microsoft SQL Server installations must take control of their environments in order to experience significant cost savings and increased activity. Thankfully, enhancements in hardware and software technologies have unlocked new opportunities to reduce costs through consolidation. Consolidation reduces the number of physical servers in an organization's environment, directly impacting costs in numerous areas including, but not limited to hardware, administration, power consumption, and licenses. Equally important, by leveraging the new SQL Server Utility feature in Microsoft SQL Server 2008 R2, organizations can streamline consolidation efforts because this feature provides database administrators (DBAs) with insight into resource utilization through policy evaluation and historical analysis. This chapter begins by describing the consolidation options available to DBAs. It then explains how DBAs can take advantage of viewpoints and dashboards in the SQL Server Utility to identify consolidation opportunities, which is done by monitoring resource utilization and health state for SQL Server instances, databases, and deployed data-tier applications.

SQL Server Consolidation Strategies

The goal of SQL Server consolidation is to identify underutilized hardware and improve utilization by choosing an appropriate consolidation strategy. With SQL Server, hardware could be considered to be underutilized when workloads are using less than 30 percent of server resources. However, underutilization thresholds vary based on the hardware utilized for SQL Server and the organization. Some compelling reasons for organizations to consolidate are to reduce costs, improve efficiency, address lack of physical space in the data center, create more effective service levels, standardize, and centralize management. Some common consolidation strategies organizations can apply are described in the rest of this section.

Consolidating Databases and Instances

A very common SQL Server consolidation strategy involves placing many databases on a single instance of SQL Server. This approach offers organizations improved operations through centralized management, standardization, and improved performance. For example, multiple databases belonging to the same SQL Server instance facilitates shared memory optimization, and database consolidation helps to reduce overhead due to fixed resource costs per instance. There are some limitations with database-level consolidation, however. For example, in this scenario, all databases share the same service account, maintain the same global settings, and share a single tempdb database for processing temporary workloads.



Figure 5-1 shows many databases being consolidated onto a single physical host running one instance of SQL Server.

FIGURE 5-1 Consolidating many databases onto a single physical host running one instance of SQL Server Many times, it is not possible to consolidate all of your databases onto a single instance, possibly because additional service isolation is required or a single instance cannot sustain the workload of all of the databases. In addition, a single tempdb database could be a performance bottleneck. Your organization might also find this scenario problematic if it has requirements to maintain different service level agreements for each database, if there are too many databases consolidated on the system, if databases need to be isolated for security and regulatory compliance reasons, or if databases require different collation settings. You can still consolidate databases if you have these types of requirements; however, you may need more instances or physical hosts to support your consolidation needs. For example, the diagram in Figure 5-2 illustrates the consolidation of many databases onto a single physical host running three instances of SQL Server, whereas the diagram in Figure 5-3 represents an alternative, in which many databases are consolidated onto many instances residing on two separate physical hosts.

FIGURE 5-2 Consolidating many databases onto a single physical host running three instances

FIGURE 5-3 Consolidating many databases onto multiple physical hosts running multiple instances of SQL Server

Consolidating SQL Server Through Virtualization

Another SQL Server consolidation strategy attracting interest is virtualization. Virtualization's growing popularity is based on many factors, including its ability to significantly reduce total cost of ownership (TCO) and the number of physical servers within an infrastructure. Benefits include the need for fewer physical servers, as well as lower licensing costs. At the heart of all the excitement over virtualization is Live Migration. This new, built-in feature is a Windows Server 2008 R2 Hyper-V enhancement. Live Migration increases high availability and improves service by reducing planned outages. It allows DBAs to move SQL Server virtual machines (VMs) between physical Hyper-V hosts without any perceived interruption in service. Hyper-V on Windows Server 2008 R2 also allows for maximum scalability because it supports up to 64 logical processors. As a result, it is possible to virtualize and consolidate numerous SQL Server instances, databases, and workloads onto a single host. Another benefit is that Live Migration allows an organization to not only completely isolate its operating system with virtualization but also to host multiple editions of SQL Server while running both 32-bit and 64-bit versions within a single host. In addition, physical SQL Servers can easily be virtualized by using the physical-to-virtual (P2V) migration tool included with System Center Virtual Machine Manager 2008 R2. Figure 5-4 illustrates a consolidation strategy in which many databases, instances, and physical SQL Server systems are virtualized on a single Hyper-V host.

FIGURE 5-4 Consolidating many databases, instances, and physical hosts with virtualization

No matter what consolidation strategy an organization adapts, the benefits are significant without any sacrifice of scalability and overall performance. Now that the consolidation strategies have been explained, it is time to explore how an organization can quickly recognize whether its database environment is a candidate for consolidation and can ultimately streamline its consolidation efforts by monitoring resource utilization.

Using the SQL Server Utility for Consolidation and Monitoring

The SQL Server Utility is the center of operations for monitoring managed instances of SQL Server, databases, and deployed data-tier applications. By using the dashboards and viewpoints included in the SQL Server Utility, DBAs can proactively monitor and view resource utilization, health state, and health policies for managed instances, databases, and deployed data-tier applications at scale. The results obtained from monitoring allow DBAs to easily identify consolidation candidates across an organization's database environment. To experience the dashboards and viewpoints yourself, launch the SQL Server Utility by following these steps: IMPORTANT Before you can carry out these steps, you must have created a Utility Control Point, and you must enroll at least one instance of SQL Server. For more information on how to do this, see Chapter 2,"Multi-Server Administration."

1. In SQL Server Management Studio, connect to the SQL Server 2008 R2 Database Engine instance in which the UCP was created.
2. Launch Utility Explorer by clicking View and then selecting Utility Explorer.
3. In the Utility Explorer navigation pane, click the Connect To Utility icon.
4. In the Connect To Server dialog box, specify the SQL Server instance running the UCP,
   select the type of authentication, and then click Connect.
5. Connection to a Utility Control Point is complete. Begin monitoring the health state
   and resource utilization by viewing the dashboards and viewpoints.

Utility Explorer in SQL Server Management Studio provides a tree view that includes nodes for monitoring and managing settings within the SQL Server Utility. The summary dashboard is automatically displayed in the Utility Explorer Content pane when you connect to a UCP. You can view additional dashboards and viewpoints by clicking the Managed Instances node or the Deployed Data-Tier Applications node in the Utility Explorer navigation pane, as displayed in Figure 5-5.

FIGURE 5-5 Utility Explorer and the navigation tree

The three main dashboards for monitoring and managing resource utilization and consolidation efforts are discussed in the next sections. These dashboards and viewpoints are

• The SQL Server Utility dashboard.
• The Managed Instance viewpoint.
• The Data-Tier Applications viewpoint.

Using the SQL Server Utility Dashboard

The SQL Server Utility dashboard is the starting place for obtaining summary information about managed instances of SQL Server and deployed data-tier applications in the SQL Server Utility. The summary of the data, as illustrated in Figure 5-6, is sectioned into nine parts and can be viewed in the Utility Explorer Content pane by clicking a Utility Control Point, which is the top node in the Utility Explorer tree.

FIGURE 5-6 The SQL Server Utility dashboard

The SQL Server Utility dashboard includes the following information:

Utility Summary Found in the center of the top row of the Utility Explorer Content pane, this section is the first place to look. It displays the number of managed instances of SQL Server and the number of deployed data-tier applications managed by the SQL Server Utility. Use the Utility Summary section to gain quick insight into the number of objects being managed by the SQL Server Utility. In Figure 5-6, there are 14 managed instances and nine deployed data-tier applications displayed in the Utility Summary section.

NOTE After you have reviewed the summary information, it is recommended that you analyze either the managed instances or deployed data-tier application section in its entirety to gain a comprehensive understanding of its overall health status. For example, the first set of the following bullets interpret the health of managed instances. After managed instances are analyzed and explained, then the health of data-tier applications is reviewed from beginning to end.

Managed Instance Health This section is located in the top-left corner of the Utility Explorer Content pane and summarizes the health status of all managed instances of SQL Server in the SQL Server Utility. Health status is illustrated in a pie chart and has four possible designations:

• Well Utilized The number of managed instances of SQL Server that are not violating resource utilization policies is displayed.
• Overutilized A SQL Server instance is marked as overutilized if any of the following
  conditions are true:
   CPU resources for the instance of SQL Server are overutilized.
   CPU resources of the computer that hosts the SQL Server instance are overutilized.
   The instance contains data or log files with overutilized storage space.
   The instance contains data or log files that reside on volumes with overutilized storage space.
• Underutilized A SQL Server instance is marked as underutilized if it is not marked as overutilized and any of the following conditions are true:
   CPU resources allocated to the instance of SQL Server are underutilized.
   CPU resources of the computer that hosts the SQL Server instance are underutilized.
   The instance contains data or log files with underutilized storage space.
   The instance contains data or log files that reside on volumes with underutilized storage space.
• No Data Available Either data has not been uploaded from a managed instance or there is a problem with the collection and upload process.
  By viewing the Managed Instance Health section, DBAs are able to quickly obtain an overview of resource utilization across all managed instances within the utility. The example in Figure 5-6 shows that five managed instances are well utilized, six are overutilized, none are underutilized, and data is unavailable for three managed instances in the Managed Instance Health section.

Managed Instances With Overutilized Resources This section is found directly under the Managed Instance Health section. It displays overutilization data for managed instances of SQL Server based on the following categories:

• Overutilized Instance CPU This represents the number of managed instances of SQL Server that are violating instance CPU overutilization policies.
• Overutilized Database Files This represents the number of managed instances of SQL Server with database files that are violating file space overutilization policies.
• Overutilized Storage Volumes This represents the number of managed instances of SQL Server with database files on storage volumes that are violating file space overutilization policies.
• Overutilized Computer CPU This represents the number of managed instances of SQL Server running on computers that are violating computer CPU overutilization policies.
  
  Detailed status for each health parameter is listed in a sliding indicator to the right of each element in this section.
  
  Managed Instances With Underutilized Resources This section is located under the Managed Instances With Overutilized Resources section and displays underutilization data for managed instances of SQL Server based on the following categories:
• Underutilized Instance CPU This represents the number of managed instances of SQL Server that are violating instance CPU underutilization policies.
   
• Underutilized Database Files This represents the number of managed instances of SQL Server with database files that are violating volume space underutilization policies.
   
• Underutilized Storage Volumes This represents the number of managed instances of SQL Server with database files on storage volumes that are violating file space underutilization policies.
   
• Underutilized Computer CPU This represents the number of managed instances of SQL Server running on computers that are violating computer CPU underutilization policies.
  
  Detailed status for each health parameter is listed in a sliding indicator to the right of each element in this section.

Data-Tier Application Health This section is located in the top-right corner of the Utility Explorer Content pane. Health status is illustrated in a pie chart and has four possible designations:

• Well Utilized The number of deployed data-tier applications that are not violating resource utilization policies is displayed.
• Overutilized The number of deployed data-tier applications that are violating resource overutilization policies is displayed. A deployed data-tier application is marked as overutilized if any of the following conditions are true:  CPU resources for the deployed data-tier application are overutilized.  CPU resources of the computer that hosts the SQL Server instance are overutilized.
   Storage volumes associated with the deployed data-tier application are overutilized.
   The deployed data-tier application contains data or log files that reside on volumes with overutilized storage space.
   
• Underutilized The number of deployed data-tier applications that are violating resource underutilization policies is displayed. A deployed data-tier application is marked as underutilized if any of the following conditions are true:
   CPU resources for the deployed data-tier application are underutilized.
   CPU resources of the computer that hosts the SQL Server instance are underutilized.
   Storage volumes associated with the deployed data-tier application are underutilized.
   The deployed data-tier application contains data or log files that reside on volumes with underutilized storage space.
   
• No Data Available Either data affiliated with deployed data-tier applications has not been uploaded to the Utility Control Point or there is a problem with the collection and upload process.
  By viewing the Data-Tier Application Health section, DBAs can quickly obtain a holistic view of resource utilization for all deployed data-tier applications managed by the SQL Server Utility. In Figure 5-6, there are seven well-utilized and two overutilized data-tier applications.

Data-Tier Applications With Overutilized Resources This section is found directly under the Data-Tier Application Health section. It displays overutilization data for deployed data-tier applications based on the following categories:

• Overutilized Data-Tier Application CPU This represents the number of deployed data-tier applications that are violating data-tier application CPU overutilization policies.
   
• Overutilized Database Files This represents the number of deployed data-tier applications with database files that are violating file space overutilization policies.
   
• Overutilized Storage Volumes This represents the number of deployed datatier applications with database files on storage volumes that are violating file space overutilization policies.
   
• Overutilized Computer CPU This represents the number of deployed data-tier applications running on computers that are violating computer CPU overutilization policies.
  
  Detailed status for each health parameter is listed in a sliding indicator to the right of each element in this section.
  
  Data-Tier Applications With Underutilized Resources This section is located directly under the Data-Tier Applications With Overutilized Resources section. This section displays underutilization data of individual instances based on the following categories:
   
• Underutilized Data-Tier Application CPU This represents the number of deployed data-tier applications that are violating data-tier application CPU underutilization policies.
   
• Underutilized Database Files This represents the number of deployed data-tier applications with database files that are violating file space underutilization policies.
   
• Underutilized Storage Volumes This represents the number of deployed datatier applications with database files on storage volumes that are violating file space underutilization policies.
   
• Underutilized Computer CPU This represents the number of deployed datatier applications running on computers that are violating computer CPU underutilization policies.
  Detailed status for each health parameter is listed in a sliding indicator to the right of each element in this section.

Utility Storage Utilization History Located at the bottom-left corner of the Utility Explorer Content pane, this section uses a time graph to display the storage utilization history for the amount of storage the SQL Server Utility is consuming in gigabytes. By using the buttons under the Interval heading , you can view data in the graph by the following intervals:

• 1 Day Displays data in 15-minute intervals
• 1 Week Displays data in one-day intervals
• 1 Month Displays data in one-week intervals
• 1 Year Displays data in one-month intervals

Utility Storage Utilization The bottom-right corner shows a pie chart that displays the amount of space used and the amount of free space available on the volume hosting the SQL Server Utility. It is worth noting that the data is refreshed every 15 minutes.

This section explained how to obtain summary information for all managed instances of SQL Server. DBAs seeking more information might be interested in the Managed Instances node in the tree view of Utility Explorer. This node helps database administers gain deeper knowledge of health status and resource utilization data for each managed instances of SQL Server. The next section discusses this dashboard.

TIP When working with the SQL Server Utility dashboard, you can click on a link to reveal additional details about a specific policy.

Using the Managed Instances Viewpoint

DBAs can display the Managed Instances viewpoint in the Utility Explorer Content pane by connecting to a UCP and then selecting the Managed Instances node in the Utility Explorer tree. The Utility Explorer Content pane displays the viewpoint, as shown in Figure 5-7, which communicates the health state and resource utilization information for numerous items including the CPU, storage, and policies for each managed instance of SQL Server.

FIGURE 5-7 The Managed Instances viewpoint

Resource utilization for each managed instance of SQL Server is presented in the list view located at the top of the Utility Explorer Content pane. Health state icons appear to the right of each managed instance and provide summary status for each instance of SQL Server based on the utilization category. Three icons are used to indicate the health of each managed instance of SQL Server. A green check mark indicates that an instance is well utilized and does not violate any policies. A red arrow indicates that an instance is overutilized, and a green arrow indicates underutilization. The lower half of the dashboard contains tabs for CPU utilization, storage utilization, policy details, and property details for each managed instance. In Figure 5-7, the instance CPU, computer CPU, file space, and volume space columns for SQL2K8R2-01\INSTANCE01 and SQL2K8R2-01\INSTANCE05 are all underutilized. In addition, the following other elements are underutilized: the Instance CPU for SQL2K8R2-03\ INSTANCE03, Computer CPU for SQL2K8R2-01\INSTANCE02, SQL2K8R2-01\INSTANCE03, SQL2K8R2-01\INSTANCE04 and SQL2K8R2-01\INSTANCE05, File Space for SQL2K8R2-02\INSTANCE03 and Volume Space for SQL2K8R2-01\INSTANCE02, SQL2K8R2-01\INSTANCE03, and SQL2K8R2-01\INSTANCE04. The volume space for SQL2K8R2-02, SQL2K8R2-02\INSTANCE02, SQL2K8R2-03, SQL2K8R2-03\INSTANCE02, SQL2K8R2-03\INSTANCE03, and SQL2K8R2-03\ INSTANCE04 are all overutilized, and the remainder of managed instances are well utilized. The Managed Instances list view columns and utilization tabs are discussed in more detail in the next sections.

The Managed Instances List View Columns

The health status of each managed instance of SQL Server in the Managed Instances list view
is analyzed against four types of utilization and the current policy in place for each:

• Instance CPU This column indicates processor utilization of the managed instance. The health state is determined by the global CPU Utilization For All Managed Instances Of SQL Server policy, which is predetermined for all managed instances of SQL Server. However, by clicking on the Policy Tab in the bottom half of the view, DBAs can override this global policy to configure overutilization and underutilization policies for a single instance. The CPU Utilization tab shows the CPU utilization history for the selected managed instance of SQL Server.
   
• Computer CPU This column communicates computer processor utilization where the managed instance resides. Health is based on the settings of two policies: the CPU utilization policy in place for the computer and the configuration setting for the Volatile Resource Evaluation policy. The CPU Utilization tab shows the processor utilization history for a managed instance of SQL Server.
   
• File Space The File Space column summarizes file space utilization for all of the databases belonging to a selected instance of SQL Server. The health state for this parameter is determined by global or local file space utilization policies. Because there are many database associated with a managed instance of SQL server, the health state is reported as overutilized if only one database is overutilized. The Storage Utilization tab shows health state information on all other database files.
   
• Volume Space Volume space utilization is summarized in this column for volumes with databases belonging to each managed instance. The health of this parameter is determined by the global or local storage volume utilization policies for managed instances of SQL Server. As with file space reports, the health of a storage volume associated with a managed instance of SQL Server that is overutilized is reported with a red up arrow, and underutilization is reported with a green arrow. The Storage Utilization tab shows additional health information and history for volumes.
   
• Policy Type The final column in the list view specifies the type of policy applied to the managed instance of SQL Server. Policy type results are reported as either Global or Override, with Global meaning that default policies are in use, and Override meaning that custom policies are in use.

DBAs can appreciate the value of the information each list view column holds. But, in the case of the Managed Instances view, DBAs can gain an even greater appreciation by also accessing the Managed Instances viewpoint tabs to better understand their present infrastructure and to better prepare for a successful consolidation.

The Managed Instances Detail Tabs

The Managed Instances viewpoint includes tabs for additional viewing. The tabs are located at the bottom of the viewpoint and consist of

CPU Utilization The CPU Utilization tab, illustrated earlier in Figure 5-7, displays historical information of CPU utilization for a selected managed instance of SQL Server according to the interval specified on the left side of the display area. DBAs can change the display intervals for the graphs by selecting one of these options:

• 1 Day Displays data in 15-minute intervals
• 1 Week Displays data in one-day intervals
• 1 Month Displays data in one-week intervals
• 1 Year Displays data in one-month intervals
  
  Two linear graphs are presented next to each other. The first graph shows CPU utilization based on the managed instance of the SQL Server, and the second graph displays data based on the computer associated with the managed instance.

Storage Utilization The next tab displays storage utilization for a selected managed instance of SQL Server, as depicted in Figure 5-8. Data is grouped by either database or volume. When the Database option button is selected, storage utilization is displayed for each database, filegroup, or a specific database file, which is based on the node selected in the tree view. If the Volume option button is selected, storage utilization history is displayed according to file space used by all data files and all log files located on the storage volume. The tree view also can be expanded to present storage utilization information and history for each volume and database file associated with a volume.

FIGURE 5-8 The Storage Utilization tab on the Managed Instances viewpoint

Independent of how the files are grouped, health status is communicated for every database, filegroup, database file, or volume. For example, the green arrows in Figure 5-8 indicate that all databases, filegroups, and data files are underutilized. No health states are shown as overutilized. Once again, the display intervals for the graphs are changed by selecting one of the following options:

• 1 Day Displays data in 15-minute intervals
• 1 Week Displays data in one-day intervals
• 1 Month Displays data in one-week intervals
• 1 Year Displays data in one-month intervals

Policy Details DBAs can use the Policy Details tab, shown in Figure 5-9, to view the global policies applied to a selected managed instance of SQL Server. In addition, the Policy Details tab can be used to create a custom policy that overrides the default global policy applied to a selected managed instance of SQL Server. The display is broken into the following four policies that can be viewed or modified:

• Managed Instance CPU Utilization Policies
• File Space Utilization Policy
• Computer CPU Utilization Policies
• Storage Volume Utilization Policies

FIGURE 5-9 The Policy Details tab on the Managed Instances viewpoint

NOTE To override the global policy for a specific managed instance, select the Override The Global Policy option button. Next, specify the new overutilized and underutilized numeric values in the control boxes to the right of the policy description, and then click Apply. For example, in Figure 5-9, the default global policy for the CPU of a managed instance is to consider the CPU overutilized when its usage is greater than 70 percent. The global policy was overridden, and the new setting is 50 percent. Similarly, the CPU underutilization setting is changed from zero percent to 10 percent.

Property Details This tab, shown in Figure 5-10, displays property details for the selected managed instance of SQL Server. The Property detail information displays the processor name, processor speed, processor count, physical memory, operating system version, SQL Server version, SQL Server edition, backup directory, collation information, case sensitivity, language, whether or not the instance of SQL Server is clustered, and the last time data was successfully updated.

FIGURE 5-10 The Property Details tab on the Managed Instances viewpoint

Using the Data-Tier Application Viewpoint

As it is when you use the Managed Instances viewpoint to monitor health status and resource utilization for managed instances of SQL Server, using the Data-Tier Applications viewpoint enables you to monitor deployed data-tier applications managed by the SQL Server Utility Control Point.

NOTE The viewpoints associated with this section may at first appear identical to the information under the previous section, "The Managed Instances Detail Tab." However, the policies and files in this section do differ from those described previously, sometimes slightly and sometimes significantly.

Similar to the Managed Instance viewpoint, DBAs can access the Data-Tier Applications view and viewpoints in the Utility Explorer Content pane by connecting to a UCP and then selecting the Deployed Data-Tier Application node in the Utility Explorer tree. The Utility Explorer Content pane displays the view, as illustrated in Figure 5-11, that communicates the health and utilization status for the application CPU, the computer CPU, file space, and volume space.

FIGURE 5-11 The data-tier application viewpoint

Resource utilization for each deployed data-tier application is presented in the list view located at the top of the Utility Explorer Content pane. Health state icons appear at the right of each deployed data-tier application and provide summary status for each deployed data-tier application based on the utilization category. Three icons are used to indicate the health state of each deployed data-tier application. A green check mark indicates that the deployed datatier application is well utilized and does not violate any policies. A red arrow indicates that the deployed data-tier application is overutilized, and a green arrow indicates underutilization. The lower half of the view contains tabs for CPU utilization, storage volume utilization, access policy definitions, and property details for each data-tier application. For example, the computer CPU and volume space for the AccountingDB and FinanceDB data-tier applications shown in Figure 5-11 are underutilized. In addition, the application CPU and the file space utilization for all deployed data-tier applications are well utilized, and the volume space for AdventureWorks2005 and AdventureWorks2008R2 are overutilized.

The data-tier application list view columns and utilization tabs are discussed in the upcoming sections.

The Data-Tier Application List View

The columns presenting the state of health for each deployed data-tier application in the
data-tier application list view include

• Application CPU This column displays the health state utilization of the processor for the deployed data-tier application. The health state is determined by the CPU utilization policy for deployed data-tier applications. The CPU Utilization tab shows CPU utilization history for the selected deployed data-tier application. Computer CPU This column communicates computer processor utilization for deployed data-tier applications. The CPU Utilization tab shows the processor utilization history for the deployed data-tier application.
• File Space The File Space column summarizes file space utilization for each deployed data-tier application. The health state for this parameter is determined by global or local file space utilization policies. The Storage Utilization tab shows health state information on all other database files.
• Volume Space Volume space utilization is summarized in this column for volumes with databases belonging to each deployed data-tier application. The health of this parameter is determined by the global or local Storage Volume utilization policies for deployed data-tier application of SQL Server. Similar to File Space reports, the health of a storage volume associated with a deployed data-tier application of SQL Server that is overutilized is reported with a red arrow, and underutilization is reported with a green arrow. The Storage Utilization tab shows additional health information and history for volumes.
• Policy Type This column in the list view specifies the type of policy applied to a deployed data-tier application of SQL Server. Policy Type results are reported as either Global or Override. Global indicates that default policies are in use, and Override indicates that custom policies are in use.
• Instance Name The final column in the list view specifies the name of the SQL Server instance to which the data-tier application has been deployed.

The Data-Tier Application Tabs

The Data-Tier Applications viewpoint includes tabs for additional viewing. The tabs are located at the bottom of the viewpoint and consist of

CPU Utilization The CPU Utilization tab, illustrated in Figure 5-11, displays historical information on CPU utilization for a selected deployed data-tier application according to the interval specified on the left side of the display area. DBAs can change the display intervals for the graphs by selecting one of the following options:

• 1 Day Displays data in 15-minute intervals
• 1 Week Displays data in one-day intervals
• 1 Month Displays data in one-week intervals
• 1 Year Displays data in one-month intervals

Two linear graphs are presented next to each other. The first graph shows CPU utilization based on the selected deployed data-tier application, and the second graph displays data based on the computer associated with the deployed data-tier application.

Storage Utilization The next tab displays storage utilization for a selected deployed data-tier application, as depicted in Figure 5-12. Data is grouped by either filegroup or volume. When the Filegroup option button is selected, storage utilization is displayed for each data-tier application based on the node selected in the tree view. If the Volume option button is selected, storage utilization history is displayed by volume. The tree view also can be expanded to present storage utilization information and history for each volume and filegroup associated with a deployed data-tier application. In Figure 5-12, the volume space for the AdventureWorks2005 deployed data-tier application is shown as overutilized because a red arrow is displayed in the Volume Space column of the Storage Utilization tab.

Once again, the display intervals for the graphs are changed by selecting one of the options available below:

• 1 Day Displays data in 15-minute intervals
• 1 Week Displays data in one-day intervals
• 1 Month Displays data in one-week intervals
• 1 Year Displays data in one-month intervals

FIGURE 5-12 The Storage Utilization tab on the Data-Tier Applications viewpoint

Policy Details: The Policy Details tab, shown in Figure 5-13, is where a DBA can view the global policies applied to a selected deployed data-tier application. The Policy Details tab can also be used to create a custom policy that overrides the default global policy applied to a deployed data-tier application. For example, by expanding the Data-Tier Application CPU Utilization Policies section, you can observe that the global policy is applied. With this policy, a CPU of a data-tier application is considered to be overutilized when its usage is greater than 70 percent and underutilized when it is less than zero percent. If you wanted to override this global policy for a data-tier application, you would select the Override The Global Policy option button and specify the new overutilized and underutilized numeric values in the box. You would then click Apply to enforce the new policy. In Figure 5-13, the global policy has been modified from its original settings, and the CPU of a data-tier application is now considered to be overutilized when its usage is greater than 30 percent. To override this setting, you would choose the Override The Global Policy option button and set a desired value in the box to the right of the policy description. For this example, the setting was changed from 30 percent to 70 percent.

FIGURE 5-13 The Policy Details tab on the Data-Tier Applications viewpoint

The display is broken up into the following four policies, which can be viewed or
overridden:

• Data-Tier Application CPU Utilization Policies
• File Space Utilization Policies
• Computer CPU Utilization Policies
• Storage Volume Utilization Policies

Property Details: The Property Details tab, shown in Figure 5-14, displays generic property details for the selected deployed data-tier application. Property detail information consists of database name, deployed date, trustworthiness, collation, compatibility level, encryption-enabled state, recovery model, and the last time data was successfully updated.

FIGURE 5-14 The Property Details tab on the Data-Tier Applications viewpoint