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 184.108.40.2062 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
Slow - Foxhound screenshot with DEDICATED_TASK = OFF for 100 connections
asked 07 Feb '15, 15:50
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.
answered 07 Feb '15, 16:50