<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Server Utility Explorer - the monitoring tool from SSMS

Published on
5,963 Points
1,263 Views
2 Endorsements
Last Modified:
Looking for a way to monitor resources health and usage from SQL Server instances and databases without expending more money and much time?
Then this article is for you.
You only need to have an Enterprise Edition of SQL Server 2008R2 or superior so you can use the SQL Server Utility component.
Utility Explorer is a component of SQL Server Management Studio that provides information about the health state of managed instances of SQL Server 2008R2 or superior, providing a user interface to view and manage policy definitions.
A Windows Domain Account is also necessary to configure the Utility Control Point (UCP).
NOTE: Monitoring of Data-Tier Applications is not covered by this article.

Utility Explorer is not visible in the SQL Server Management Studio by default. To view the Utility Explorer content pane, open the View menu and click on Utility Explorer.
Utility-Explorer.PNGAt the first time you need to follow the Utility Configuration Steps, starting with the creation of an Utility Control Point (UCP).
Just click on "Create Utility Control Point (UCP)" link and follow the respective wizard.

UtilityConfigurationSteps.png

I won't provide the screenshots for the wizard because it's very trivial and there's a link for an help video in case you need it.
Just remember that SQL Server Agent Service need to be running (set it to start automatically to avoid future issues) and that you need to provide a Windows Domain User Account for this component.
NOTE: The SQL Server Agent service account must have read permission on Active Directory users and must be a member of the local group Performance Monitor User.

What the creation of a UCP does is to create and configure the Utility Management DataWarehouse (UMDW). After that you will see the dashboard in Utility Explorer Content:
UtilityExplorerContent.png
The dashboard it still blank because the datawarehouse has no data but at least you can see already what kind of information and how it will be provided.

Going back to ObjectExplorer we can confirm that the datawarehouse (sysutility_mdw) has been created:
UtilityExplorerDB.png

Now it's time to configure the Policies, Security and the Datawarehouse. For that, in the Utility Explorer pane click on Utility Administration option. The Utility Explorer Content will change to something similar to the below screen:
UtilityExplorerConfig.png
Here you can set the Health Policies (thresholds), Security (permissions) and the Datawarehouse data retention period (by default it's one year) and collection upload frequency (by default every 15 minutes). I recommend you to leave the defaults values and later if necessary tweak as your need.

After configuration you can start to enroll your SQL Server instances one by one, by right-clicking on Managed Instances and chose "Enroll Instances" and will appear a wizard very similar with the wizard for create an UCP. Once more it's a very trivial wizard and that's why and won't provide screenshots for that.
Just remember that each enrolled instance must have it owns SQL Server Agent Service running and that you need to provide the Windows Domain User that is used for the SQL Agent account of the UCP server. Also you can't enroll the SQL Server instance where the UCP's data warehouse is running and can only enrolling SQL Server instances from another domain if there is a 2 way trusts between the domains.

You can see the existence of 2 SQL Agent jobs in each MSSQL instance that has been enrolled in UCP:
UCP-Jobs.PNG
If those jobs aren't running then it's mostly possible that the security policy doesn't let you run PowerShell scripts. This can be confirmed by verifying in the Job history if the error returned is:
"Executed as user: Domain\UserAccount. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error.  '.  Process Exit Code -1.  The step failed."

If so check what's the Execution Policy configured by running the following PowerShell command: Get-ExecutionPolicy.
You'll need to set it to Unrestricted and if you don't have the necessary privileges to change it then contact your system administrator to change this policy for you. The PowerShell command is:
Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy Unrestricted

Open in new window

Others kind of errors can occur during the enroll phase. For more information on those errors please read Troubleshoot the SQL Server Utility.

By default the above jobs are running every 15 minutes and collecting data into the datawarehouse (sysutility_mdw). Let it pass some time (one hour or more) to have some data to be analyzed. Below is an example of how the dashboard in UCP Explorer can looks like:
UCP-Explorer-Content.PNGGo to Manage Instances to check the available data.

Here you'll find information about CPU and Storage health and usage. Below are two sample images of what kind of information you can get from UCP and I really challenge you to dig around to know more about the UCP capabilities.
UCP-CPU.PNGUCP-Storage.PNG
 
As you can see there's no code to run and it's very easy to set a monitoring solution only with SQL Server resources. Unfortunately UCP monitors only CPU and Storage. I would like that in a future version Microsoft will also add Memory monitoring.
Some considerations:
  • The average disk space used by the datawarehouse (sysutility_mdw) is approximately 2 GB per managed instance of SQL Server per year.
  • 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.
  • Each managed instance of SQL Server and every data-tier application is a member of one and only one SQL Server Utility and managed by a single UCP only.
  • When enrolling a SQL Server instance that has Data-tier applications, they are automatically enrolled as well.
2
2 Comments
LVL 67

Expert Comment

by:Jim Horn
Nicely done.  Voting Yes.
0
LVL 16

Expert Comment

by:Deepak Chauhan
Good Article.
0

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month