Microsoft SQL Server 2008 R2 Administration Cookbook

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook

(For more resources on Microsoft SQL Server, see here.)

The UCP collects configuration and performance information that includes database file space utilization, CPU utilization, and storage volume utilization from each enrolled instance. Using Utility Explorer helps you to troubleshoot the resource health issues identified by SQL Server UCP. The issues might include mitigating over-utilized CPU on a single instance or multiple instances.

UCP also helps in reporting troubleshooting information using SQL Server Utility on issues that might include resolving a failed operation to enroll an instance of SQL Server with a UCP, troubleshooting failed data collection resulting in gray icons in the managed instance list view on a UCP, mitigating performance bottlenecks, or resolving resource health issues.

The reader will benefit by referring the previous articles on Best Practices for SQL Server 2008 R2 Administration and Managing the Core Database Engine before proceeding ahead.

Getting ready

The UCP and all managed instances of SQL Server must satisfy the following prerequisites:

• UCP SQL Server instance version must be SQL Server 2008 SP2[10.00.4000.00] or higher
• The managed instances must be a database engine only and the edition must be Datacenter or Enterprise on a production environment
• UCP managed account must operate within a single Windows domain or domains with two-way trust relationships
• The SQL Server service accounts for UCP and managed instances must have read permission to Users in Active Directory

To set up the SQL Server Utility you need to:

• Create a UCP from the SQL Server Utility
• Enroll data-tier applications
• Enroll instances of SQL Server with the UCP
• Define Global and Instance level policies, and manage and monitor the instances.

Since the UCP itself becomes a managed instance automatically, once the UCP wizard is completed, the Utility Explorer content will display a graphical view of various parameters, as follows:

How to do it...

To define the global and instance level policies to monitor the multiple instances, use the Utility Explorer from SSMS tool and complete the following steps:

1. Click on Utility Explorer; populate the server that is registered as utility control point.
2. On the right-hand screen, click on the Utility Administration pane.
3. The evaluable time period and tolerance for percent violations are configurable using Policy tab settings.
4. The default upper threshold utilization is 70 percent for CPU, data file space, and storage volume utilization values. To change the policies use the slider-controls (up or down) to the right of each policy description.
5. For this recipe, we have modified the upper thresholds for CPU utilization as 50 percent and data file space utilization as 80 percent. We have also reduced the upper limit for the storage volume utilization parameter.
6. The default lower threshold utilization is 0 percent for CPU, data file space, and storage volume utilization values. To change the policies, use the slider-controls (up only) to the right of each policy description.

   For this recipe, we have modified (increased) the lower threshold for CPU utilization to 5 percent.

   Unlock access to the largest independent learning library in Tech for FREE!

   Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.

   Renews at €18.99/month. Cancel anytime

   START FREE TRIAL

7. Once the threshold parameters are changed, click Apply to take into effect. For the default system settings, either click on the Restore Defaults button or the Discard button, as shown in the following screenshot:

Now, let us test whether the defined global policies are working or not.

1. From the Query Editor, open a new connection against SQL instances, which is registered as Managed Instance on UCP, and execute the following time-intensive TSQL statements:

   create table test (
   x int not null,
   y char(896) not null default (''),
   z char(120) not null default('')
   )
   go
   insert test (x)
   select r
   from
   (
   selectrow_number() over (order by (select 1)) r
   from master..spt_values a, master..spt_values b
   ) p
   where r <= 4000000
   go
   create clustered index ix_x on test (x, y)
   with fillfactor=51
   go

   
   

2. The script will simulate a data load process that will lead into a slow performance on managed SQL instance. After a few minutes, right-click on the Managed Instances option on Utility Explorer, which will produce the following screenshot of managed instances:

   

3. In addition to the snapshot of utilization information, click on the Managed Instances option on Utility Explorer to obtain information on over-utilized database files on an individual instance (see the next screenshot):

We should now have completed the strategic steps to manage multiple instances using the Utility Explorer tool.

How it works...

The unified view of instances from Utility Explorer is the starting point of application and multi-server management that helps the DBAs to manage the multiple instances efficiently.

Within the UCP, each managed instance of SQL Server is instrumented with a data collection set that queries configuration and performance data and stores it back in UMDW on the UCP every 15 minutes. By default, the data-tier applications automatically become managed by the SQL Server utility. Both of these entities are managed and monitored based on the global policy definitions or individual policy definitions.

Troubleshooting resource health issues identified by an SQL Server UCP might include mitigating over-utilized CPU on a computer on an instance of SQL Server, or mitigating over-utilized CPU for a data-tier application. Other issues might include resolving over-utilized file space for database files or resolving over-utilization of allocated disk space on a storage volume. The managed instances health parameter collects the following system resource information:

• CPU utilization for the instance of SQL Server
• Database files utilization
• Storage volume space utilization
• CPU utilization for the computer

Status for each parameter is divided into four categories:

• Well-utilized: Number of managed instances of an SQL Server, which are not violating resource utilization policies.
• Under-utilized: Number of managed resources, which are violating resource underutilization policies.
• Over-utilized: Number of managed resources, which are violating resource overutilization policies.
• No Data Available: Data is not available for managed instances of SQL Server as either the instance of SQL Server has just been enrolled and the first data collection operation is not completed, or because there is a problem with the managed instance of SQL Server collecting and uploading data to the UCP.

The data collection process begins immediately, but it can take up to 30 minutes for data to appear in the dashboard and viewpoints in the Utility Explorer content pane. However, the data collection set for each managed instance of an SQL Server will send relevant configuration and performance data to the UCP every 15 minutes.

Summary

This article on SQL Server 2008 R2 covered Multiserver Management Using Utility Explorer.

• Best Practices for Microsoft SQL Server 2008 R2 Administration
• Microsoft SQL Server 2008 R2: Managing the Core Database Engine [Article]
• Managing Core Microsoft SQL Server 2008 R2 Technologies [Article]
• SQL Server 2008 R2 Technologies: Deploying Master Data Services [Article]
• Getting Started with Microsoft SQL Server 2008 R2 [Article]
• Microsoft SQL Server 2008 - Installation Made Easy [Article]
• Creating a Web Page for Displaying Data from SQL Server 2008 [Article]
• Ground to SQL Azure migration using MS SQL Server Integration Services [Article]
• Microsoft SQL Server 2008 High Availability: Understanding Domains, Users, and Security [Article]