Managing the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 107

The Managed Instances node is presented as a tree view to access list view data and details
tabs at the bottom of the content pane providing data for CPU and storage volume utilization,
as well as access to policy definitions and property details for individual managed instances
of SQL Server in the SQL Server Utility. Within the Managed Instances the default maximum
values for instance processor utilization/computer process utilization/file space utilization
is 70 percent that produces a trend of resource usage and the default minimum value for
instance processor utilization/computer process utilization/file space is 0 percent.
Implementing Utility & Non-utility collection
sets
The Utility information data collection set is installed and automatically started on each
instance of SQL Server 2008 R2 when you complete the Utility Control Point (UCP) as we have
seen in the previous recipes of this chapter. The data is stored in the UMDW database, which is
created during the UCP creation. The SQL Server utility collection set is supported side-by-side
with Utility collection sets and non-SQL Server utility collection sets. In this recipe, we will
go through the implementation tasks to set up the UCP data collection sets for utility and
non-utility categories.
SQL Server 2008 R2 introduces the Utility Control Point (UCP) with a set of pre-defined utility
collection sets that are managed by UMDW. Similarly, SQL Server 2008 manages the data
collection to monitor CPU, disk, and memory resources of an instance using a Data Collector
that is managed by Management Data Warehouse (MDW). For this recipe, it is necessary to
introduce the MDW feature that stands as a non-utility collection set. The Management Data
Warehouse is a relational database that contains all the data that is retained. This database
can be on the same system as the data collector, or it can be on another computer. The MDW
collection set is run in one of the following collection and upload modes:
F Non-cached mode: Data collection and upload are on the same schedule. The
packages start, collect, and upload data at their configured frequency, and run until
they are finished. After the packages finish, they are unloaded from memory.
F Cached mode: Data collection and upload are on different schedules. The packages
collect and cache data until they receive a signal to exit from a loop control-flow
task. This ensures that the data flow can be executed repeatedly, which enables
continuous data collection.
Getting ready
The new feature of SQL Server 2008 R2—Utility Control Point (UCP)—allows DBAs to set up
and collect the utility collection sets. Once the instances are enrolled, the default capacity
policies of utilization across the instances or applications are set. It is essential to check that
you are using a SQL Server 2008 R2 instance to register the UCP to design the multi-server
management feature.