The Help seems to imply that DEDICATED_TASK can (or should) only be set to On for one single connection.

It also says that "a request handling task is dedicated exclusively to handling requests for the connection".

However, testing with 16.0.0.2052 indicates that something far less dramatic may be the actual case.

First of all, it is possible to set DEDICATED_TASK to On for many connections, not just one; for example:

SELECT COUNT(*) AS "Connections with DEDICATED_TASK = ON"
  FROM sa_connection_properties()
 WHERE PropName = 'dedicated_task'
   AND Value = 'On';
Connections with DEDICATED_TASK = ON 
------------------------------------ 
                                 100 

Second, when those connections are used in an "overclocking" benchmark that pounds the server with an unrelenting series of UPDATE statements with no "think time" at all, the test runs faster with DEDICATED_TASK = On (582 seconds) than with DEDICATED_TASK = Off (679 seconds).

PLEASE NOTE: This posting DOES NOT IMPLY that setting DEDICATED_TASK = On for many connections is a good idea; the benchmark shown here is designed for DESTRUCTIVE TESTING, not evaluating performance improvements.

For the record, setting DEDICATED_TASK = On does seem to dramatically affect the dynamic tuning of the multiprogramming level. In particular, with DEDICATED_TASK set to Off for all 100 connections, the multiprogramming level (Max Req in the Foxhound screenshots below) did not rise above the default 20 very often, and stayed BELOW 20 most of the time.

With DEDICATED_TASK set to On for all 100 connections, however, the multiprogramming level rapidly rose far above 20, peaking at 80 for long periods of time. The resulting much-lower-level for Unscheduled Requests probably accounts for the higher throughput and shorter test run.

So... what exactly does DEDICATED_TASK = On do, anyway?

And, is it OK to set it to On for, say, three connections? (e.g., a DBA connection for administrator use, a SQL Anywhere Monitor connection, and a Foxhound Database Monitor connection... yes, some folks have all three).

Fast - Foxhound screenshot with DEDICATED_TASK = ON for 100 connections

alt text

Slow - Foxhound screenshot with DEDICATED_TASK = OFF for 100 connections

alt text

asked 07 Feb '15, 15:50

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%


The DEDICATED_TASK = "ON" option reserves the current worker handling the connection's request within the server for the current connection and will not use the worker for any other purpose. The intent of this option is to ensure that the connection can actually get requests into the server and do work when needed - for example for a DBA to diagnose server issues.

Each time a worker is reserved for a connection it is taken out of the common pool of workers that are used for processing requests thus effectively reducing the multiprogramming level of the common requests. If too many connections reserve dedicated tasks then you could starve connections (that do not have a dedicated worker) from being able to get their requests processed.

When a request is sent to the server on a connection that has a dedicated worker the request does not get put into the server's common request queue but instead gets dispatched immediately to the dedicated worker. This slight change in how requests are processed may be why you are seeing a difference in performance.

So there are two benefits to having a dedicated task: (1) the connection is guaranteed to get a worker to process a request when it is sent to the server, and (2) having a dedicated task lets the connection bypass the request queue and thus does not need to wait for all of the other requests in the queue to be processed before its request gets processed.

As you have noted it is highly NOT recommended to set DEDICATED_TASK = "ON" for general processing, but it is ok to have more than one "special" connections to use a dedicated task.

Note that having a dedicated task (worker) does not give any special rights or benefits when it comes to locks - all workers, dedicated or not, will use the same rules to acquire table, row, and internal locks.

permanent link

answered 07 Feb '15, 16:50

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265
accept rate: 40%

If too many connections reserve dedicated tasks then you could starve connections...

When using the default autotuning of the MPL, would "many connections" with dedicated tasks not lead to an increase in worker threads (as in Breck's sample, if my understanding is correct) instead of starvation (unless the maximum number of tasks has been reached, of course)?

(09 Feb '15, 03:20) Volker Barth
Replies hidden
1

Correct. As each additional dedicated task is assigned to connection(s), autotuning of MPL will automatically increase the number of workers up to the maximum setting at which point the overall server throughput will start to suffer.

(10 Feb '15, 08:18) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×241

question asked: 07 Feb '15, 15:50

question was seen: 431 times

last updated: 10 Feb '15, 08:18