T-SQL Tuesday #66: Monitoring with Utility Explorer

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Cathrine Wilhelmsen (blog | twitter) with the subject of monitoring.

The topic of the month is monitoring. We all monitor something while working with SQL Server: Maybe you’re a DBA who monitors database performance, an SSIS developer who monitors job and package execution, an application developer who monitors queries or a BI analyst who monitors report usage? Do you use T-SQL or PowerShell scripts? Have you created your own monitoring reports or applications? Have you invested in a complete monitoring solution from a vendor? What is monitored manually and what is automated with alerts? If a brand new SQL Server professional in your role asks you for advice, what do you say is the number one most important thing to monitor? Have you ever stayed awake for a week solving a problem that could have been avoided with proper monitoring? Did you implement a monitoring solution that saved your company?

I currently use so many different methods of monitoring, it was daunting to decide what to discuss. I could have written about a high level overview of everything, but that would have been nothing more than mentioning each one in order to fit it into one post. Policies, jobs, traces, audits, extended events, PowerShell…we use them all. Basically if it is not a third-party tool (spending money on any solution is tough to justify here), my office uses it or will be giving it a shot soon. Coincidentally, we just started using another form of monitoring in the past few weeks, so I will discuss some of the trials and tribulations of using the SQL Server Utility.

Utility Explorer provides a simplified dashboard for monitoring and baselining SQL Server CPU and storage resources. This feature is not available for Standard Edition, like almost anything else new and exciting. To utilize this feature, you will need to create a Utility Control Point (UCP). I’d suggest using your Central Management Server to host. Don’t have a CMS? If you have more than two servers, make one. It’s not hard. Unlike a CMS though, the UCP can monitor itself. This is one of the more obscure features added in SQL Server 2008R2, but you can access it in Management Studio by selecting View -> Utility Explorer.

GettingStarted

After selecting your instance, you need to specify the account to run the utility collection set. Using the Agent account is the easiest way to set up, but you have to use a Windows domain account as the collection account, regardless of which path you choose.

SpecifyAccount

Select the instance where data will be collected. Again, I suggest your CMS. A utility database will be created to store data, and the default collection period is one year, so make sure you have sufficient drive space or be ready to reduce your retention period. No worries though, this new dashboard will remind you if your disk space starts getting low! The utility database will grow about 2GB, per monitored instance, per year (typically), while the msdb database should grow around 20MB, per instance, per year. Warning: if you also use Data Collector, your data growth can be exponentially higher.

SpecifyInstance

Now the selected instance will be validated. The instance cannot be enrolled if it is already a member of another UCP. I’m using Collection Sets as well, so validation failed on that. You can bypass this issue by simply disabling your collection sets during the enrollment process. That will allow validation to succeed. After enrolled, you can turn collection sets back on.. The two pieces are related (they use the same database) and yet separate enough that they don’t always play well together.

Validation

Once you have your UCP created, you can begin enrolling instances, which is a very simple process. Just right-click on Managed Instances, or within the white space of Utility Explorer Content, and select Enroll Instance. Specify the instance name and the agent account, like you did above while creating the UCP, and then validate the enrollment. The instance may throw a warning about WMI. Since it’s just a warning, the enrollment will still succeed. My instances have not had any issues reporting data after throwing this warning. Researching the subject has thus been low priority, but it appears to be a permissions issue. Troubleshooting with that article has shown nothing of concern though…

Test

Once you have a few instances enrolled, open the Utility Dashboard, which will look similar to this.

UtilityDashboard

Select Managed Instances and select the tabs to view CPU usage, storage space, policy configuration for utilization pass/fails, and computer properties. Each enrolled instance will show a pass/fail mark based on the configured policies.

ManagedInstances
The Storage Utilization tab provides a quick look at your volume free space. You can drill down to see data file and log file sizes as well.
StorageUtilization
The Property Details tab summarizes the instances software and hardware.
PropertyDetails

Finally, in the Utility Administration, there are a few settings you need to be aware of. This is where you can set Global policies for storage warnings, set read privileges for user accounts on the Utility dashboards, and specify data retention. I suggest you start small and monitor how fast your sysutility database grows. Like I said earlier, combining this with Data Collection, we had massive data growth, more on that in the future.

Overall, Utility Explorer provides an easy to read dashboard for quick looks at your SQL Servers. If you are a graphical person and can spare the disk space, it’s a great feature. Sure, you can get the current settings via queries, but the really valuable part is the automatic retention which allows quick baselining of your CPU and storage. Capacity planning made easy.

StorageOverTime