SQL SERVER – Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage

I have mentioned several times on this blog that the best part of blogging is the questions I receive from readers. They are often very interesting. The questions from readers give me a good idea what other readers might be thinking as well. After reading my earlier article Simple Example to Configure Resource Governor – Introduction to Resource Governor – I received an email from a reader and we exchanged a few emails. After exchanging emails we both figured out what is going on. It was indeed interesting and reader suggested to that I should blog about it.  I asked for permission to publish his name but he does not like the attention so we will just call him Jeff. I have converted our emails into chat for easy consumption.

Jeff: Your script does not work at all. I think either there is a bug in SQL Server.
Pinal: Would you please explain in detail?
Jeff: Your code does not limit the CPU usage?
Pinal: How did you measure it?
Jeff: Well, we have third party tools for it but let us say I have limited the resources for Reporting Services and used your script described in your blog. After that I ran only reporting service workload the CPU is still used more than 100% and it is not limited to 30% as described in your script. Clearly something is wrong somewhere.
Pinal: Did you say you ONLY ran reporting server load?
Jeff: Yeah, to validate I ran ONLY reporting server load and CPU did not throttle at 30% as per your script.
Pinal: Oh! I get it here is the answer – CAP_CPU_PERCENT = 30. Use it.
Jeff: What is that, I think your earlier script says it will throttle the Reporting Service workload and Application/OLTP workload and balance it.
Pinal: Exactly, that is correct.
Jeff: You need to write more in email buddy! Just like your blogs, your answers do not make sense! No Offense!
Pinal: Hmm…feedback well taken. Let me try again.

In SQL Server 2012 there are a few enhancements with regards to SQL Server Resource Governor. One of the enhancement is how the resources are allocated. Let me explain you with examples.

Configuration: [Read Earlier Post]

Reporting Workload: MIN_CPU_PERCENT=0, MAX_CPU_PERCENT=30
Application/OLTP Workload: MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100

Example 1: If there is only Reporting Workload on the server:
SQL Server will not limit usage of CPU to only 30% workload but SQL Server instance will use all available CPU (if needed). In another word in this scenario it will use more than 30% CPU.

Example 2: If there is Reproting Workload and heavy Application/OLTP workload:
SQL Server will allocate a maximum of 30% CPU resources to Reporting Workload and allocate remaining resources to heavy application/OLTP workload.

The reason for this enhancement is for better utilization of the resources. Let us think, if there is only single workload, which we have limited to max CPU usage to 30%. The other unused available CPU resources is now wasted. In this situation SQL Server allows the workload to use more than 30% resources leading to overall improved/optimized performance. However, in the case of multiple workload where lots of resources are needed the limits specified in MAX_CPU_PERCENT are acknowledged.

Example 3: If there is a situation where the max CPU workload has to be enforced:
This is a very interesting scenario, in the case when the max CPU workload has to be enforced irrespective of the workload and enhanced algorithm, the keyword CAP_CPU_PERCENT is essential. It specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. It will never let CPU usage for reporting workload to go over 30% in our case. You can use the key word as follows:

-- Creating Resource Pool for Report Server

Notice that there is MAX_CPU_PERCENT=30 and CAP_CPU_PERCENT=40, what it means is that when SQL Server Instance is under heavy load under different workload it will use the maximum CPU at 30%. However, when the SQL Server instance is not under workload it will go over the 30% limit. However, as CAP_CPU_PERCENT is set to 40, it will not go over 40% in any case by limiting the usage of CPU. CAP_CPU_PERCENT puts a hard limit on the resources usage by workload.

Jeff: Nice Pinal, you should blog about it.

[A day passes by]

Pinal: Jeff, it is done! Click here to read it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to Service Broker and Sample Script

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a “conversation.” Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as “services.” If the applications need to avail the functionality of these services, then it sends message to the particular “service.”

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.

---------------------------- Service Broker -----------------------
-- In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
USE ServiceBrokerTest
-- Enable Service Broker
-- Create Message Type
-- Create Contract
-- Create Send Queue
-- Create Receive Queue
-- Create Send Service on Send Queue
ON QUEUE SBSendQueue (SBContract)
-- Create Receive Service on Recieve Queue
ON QUEUE SBReceiveQueue (SBContract)
-- Begin Dialog using service on contract
DECLARE @SBDialog uniqueidentifier
TO SERVICE 'SBReceiveService'
-- Send messages on Dialog
SET @Message = N'Very First Message';
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Second Message';
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Third Message';
MESSAGE TYPE SBMessage (@Message)
-- View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
-- Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
-- Receive messages from Receive Queue
FROM SBReceiveQueue
-- Clean Up
USE master
DROP DATABASE ServiceBrokerTest

Click to Download Scripts

Let me know what do you think of this script and how simply one can configure service broker.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Change the Port of Service Broker Configuration

Just two day ago, I wrote small note about SQL SERVER – Introduction to Service Broker.

Yesterday I wrote article where Service Broker throws error due to it is using same port as other application SQL Server – Fix – Error : 9692 The _MSG protocol transport cannot listen on port because it is in use by another process. One of the option was to configure Service Broker to different port than other application is using. I got couple of request to write how to do it.

I am writing two script where first End Point is created with specific port and required authentication and another script where End Point is dropped. This way Service Broker can be configured on different port.

Script 1: Create Service Broker End Point

USE master;

Script 2 : Drop Service Broker End Point

If any user know how to just change the port without creating and dropping Service Broker End Point, please suggest your method.

USE master ;
DROP ENDPOINT BrokerEndpoint ;

Reference : Pinal Dave (http://blog.SQLAuthority.com), Book Online

SQL Server – Fix – Error : 9692 The _MSG protocol transport cannot listen on port because it is in use by another process.

If you face following error the solution of this is very simple.

Error : 9692 The _MSG protocol transport cannot listen on port because it is in use by another process.

Above error comes up with Service Broker. Service Broker is used to send Database Emails. Read more about SQL SERVER – Introduction to Service Broker.


Option 1:

Run netstat -aon on command prompt and determine what program is using the port described in the error. Once figured out disable the application which is using that port.

Option 2:

Alternatively, the port on which Service Broker is running can be changed. See SQL SERVER – Change the Port of Service Broker Configuration.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Introduction to Service Broker

Service Broker is message queuing for SQL Server. It is used for sending emails and through Database Mails. You can read about SQL SERVER – Difference Between Database Mail and SQLMail here. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message.

So how it works is SQL Server uses Service Broker to send message from database to message queue. This message is then picked up by different processes to send this message to its destination. This separate processes can be using SMTP protocol. Sending and receiving processes can be done using entirely different system or SQL Server instance. It is not required that both the services are online at the same time. Sending services or Receiving services both are not required to be on line at the same time.

In summary, Services Broker follows traditional client-server architecture. Here client service initiates a conversation and receiving services takes messages and processes to send it to its destination.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Difference Between Database Mail and SQLMail

In recent user group meeting in my city Ahmedabad, I have found that not every user knows difference between these two features of SQL Server. I do not blame any user for not knowing difference between Database Mail and SQLMail as this is very confusing sometime. I will try to explain this concept here.

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

In summary, I suggest if you are using SQLMail, it is right time to upgrade to Database Mail.

If you still want to use SQLMail you will have to enable it with specific commands.

EXEC sp_configure 'SQL Mail XPs', 1

Again, I suggest upgrade to Database Mail and start to stop using SQLMail.

Reference : Pinal Dave (http://blog.SQLAuthority.com)