Resource Governor at Work with Azure Pack

WAP administrators can use the functionality of SQL Server 2014 Resource Governor with UR5 of Windows Azure Pack. You can read about ‘Manage tenant database workloads with resource governor in WAPack’ here. In this blog post I am detailing the last section ‘Resource Governor at Work’ of the above blog post.

Tools available to generate load on SQL server

Though we can write custom tools (C#, PowerShell) etc. to create test databases and generate load, sometimes publically available tools will come handy. Providing two tools that I have come across.

  • Hammer DB (I have used this for various load generation, benchmark verification before/after changing server configuration etc.)
  • SQL Load Generator

View CPU Usage for Resource Pools

In this post, I assume that you have followed the instructions provided in the above blog post. In my configuration, I have created the suggested resource pools and created a database named ‘contoso’. I am providing the steps to identify the resource pool name and how to view the performance counters for the resource pool.

Step 1: Identify the resource pool associated with the database

As a service provider administrator, navigate to ‘servers‘ tab in ‘SQL Servers‘ resource provider and navigate to the details of specific server.

Navigate to the ‘databases‘ tab and find out the ‘resource pool name’.

Step 2: Open performance monitor

You can do this by running ‘perfmon’ on windows run prompt.

  1. Navigate to the performance monitor view
  2. Select any existing counters
  3. Click on ‘Delete’ to remove the existing counters
  4. Click on ‘+’ button next to add new counters (in the following step)

Step 3: Add SQL Resource Governor Performance Counter

  1. Choose counter ‘CPU usage %‘ in ‘SQL Server: Resource Pool Stats

    Note: You can see that all the resource pools you saw along with databases tab in windows azure pack + two system resource pool instances (default & internal) are listed in the instances section

  2. Now you can select all instances and click on ‘Add‘ button.

Step 4: Generate load against the databases and watch usage in performance monitor

About Manesh

Manesh is a software consultant and solutions architect specializing in cloud, data, Linux and devops in the azure realm with key focus on hybrid workloads. He has been working on Azure technologies since its inception and has helped many enterprises to onboard and adapt to Azure cloud, build solutions for datacenter scale / high consumer applications and services. Currently, he is Microsoft certified for Developing Microsoft Azure Solutions (70-532), Implementing Microsoft Azure Infrastructure Solutions(70-533) and Architecting Microsoft Azure Solutions (70-534).

Posted on 2015/02/17, in Cloud-OS, SQL Server and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

My views on technology

Virtualization and some coffee

My views on technology

Thomas Maurer

Cloud and Datacenter Blog focusing on Microsoft Azure

Cloud Administrator in Azure World

Begin Your Azure Management Journey with the Cloud Administrator

%d bloggers like this: