Run T-SQL in Parallel

Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
  • Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
  • Effective monitoring: People want to see which session is running what.
  • Adjustable maximum threads in the course of execution.

Continue reading “Run T-SQL in Parallel”

Sharing Global Temp Tables

Global temp tables can be accessed by one or more sessions. This important feature of SQL Server becomes more important than it was in my programming live since more applications I worked with and am currently working on have parallel processing involved heavily. Very often, amount of data generated from one sessioin are shared to many concurrent sessions. Global temp tables come and play. Creating a global temp table is simple.

create table ##temp(i int)

It will be released when

  1. It’s explicitly removed by any sessions
  2. The creator session closed and there is no other sessions referencing this session.

Continue reading “Sharing Global Temp Tables”

Parallel Task Scheduling (7) – Scheduled By Activator

There are always concerns while writing a multi-threading program. In the last post, the considerations are degree of parallelism and tasks starving from getting workers. Max threads for one scheduler can be restricted by applying counting semaphore as I posted there. But it still does not resolve worker starvation issue. The application structure in last post is to use pool of threads managed by Service Broker to schedule tasks issued from differrent scheduler. Based on this structure, definitely you can dedicate time on coding at the activation procedure or initiator side to make the worker to go round robbin manner to excute all tasks and hence eliminate the starvation at some level. Thread starvation will still happen under the situation that large amount of long tasks are scheduled. We cannot specify thread quantum to overcome the issue. However, we can definitely utilize the nature of SQL Server to overcome the issue.
Continue reading “Parallel Task Scheduling (7) – Scheduled By Activator”

Parallel Task Scheduling (6) – Scheduled By Caller

In my last post of this series, I explain how to use message queue of service broker to deliver the tasks to and execute them at conversation target. We did not talk about complex ordering in that scenario. We can also see that the ended conversations stay in the conversation endpoint which will affect the performance of service broker. Ending a conversation with cleanup option can result this issue. It’s not suggested however. In this article, I am going to talk about how to implement complex ordering using Service Broker as a message conveyer and performer but reducing number of conversation handles.
Continue reading “Parallel Task Scheduling (6) – Scheduled By Caller”

Parallel Task Scheduling (5) – Task Conveyer

We have discussed using Service Broker as a task scheduler to read, load, and execute tasks from a queue. The issue of this approach is that the scheduler has to constantly searching the queue to find an available task and then load and execute it. While there is nothing in the queue, Service Brocker will have to perform the same operation but for nothing, in which it adds extra unnecessaryload to the system. Why not use Service Broker to perfrom functions of scheduler, queue, and executor?
Continue reading “Parallel Task Scheduling (5) – Task Conveyer”

Parallel Task Scheduling (4) – Service Broker

The biggest issue of using SQL jobs to schedule tasks is the resource consumption due to the inefficienct SQL cache utilization of SQL Server Agent. Service Broker in this case can be employed to resolve this issue. There are 2 options. One is to implement a tiny version of SQL Server Agent by Service Broker, in which we will discuss in the post, and the other way is to use Service Broker as a task conveyer and executor, which I am going to talk in next few posts in this series.
Continue reading “Parallel Task Scheduling (4) – Service Broker”

Parallel Task Scheduling (3) – Complex Ordering with SQL Jobs

In the real world, there are not only requirements on sequential or parallel process executions, but also requires complex ordering of the process executions. For instance, to complete a business process, you need to run Task 1 ~ 3 in parallel, wait until all tasks done, then perform task 4 ~ 6 at the same time. After those tasks are done, the entire process is done, or other processes may need to be lauched in the same way. In this case, how would you model it based on what we talked in last post in this series?
Continue reading “Parallel Task Scheduling (3) – Complex Ordering with SQL Jobs”

Parallel Task Scheduling (2) – Jobs

In my last post Parallel Task Scheduling (1) – Jobs, I descibed the simplest way to schedule tasks to run in parallel. It’s inefficient. Most importantly, if we have 1000 + independent tasks to schedule and each of which needs few minutes to run, it would be very possible that we end up having few hundreds jobs existing in msdb at a time before they are removed. DBAs will complain about the distractive and intermittent job creations, and also, we may not want few hundreds tasks running at same time to create CPU contention on the server with the SQL Server engine.
Continue reading “Parallel Task Scheduling (2) – Jobs”

Parallel Task Scheduling (1) – Jobs

There are more demands from different areas within a business requires things getting done quickly. Once way is to optimize the process to let it go faster, and another way is to have more hands on it in which the loads get spreaded, each person works on few smaller parts of a bigger task,  and finally getting task done in a desired time frame. Such approaches exist in the desing of SQL Server processes as well. There’re always ways to tune a procedure up, indexes, table structures, processing order, statistics, plan guides, and etc, however, you may finally find out that those do not always work as you expected. Splitting a monster process into multiple smaller independent tasks and running them in parallel come and play. In this series, I am going to talk about the ways to schedule those tasks and control the order of the executions by T-SQL. Error handling will not be discuss here since the code can always be written in the way that it handles the errors and run successfully
Continue reading “Parallel Task Scheduling (1) – Jobs”