SQL Saturday 108 Redmond

SQL Saturday 108 – Redmond Feb 25, 2012 I will be presenting Parallel Task Scheduling with T-SQL. You might already attend my presentation on this before however this session will be the revised one with new features included. It’s not so difficult to write a stored procedure to execute number of tasks (SQL statements) sequentially … Read more

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.

Read more

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.

Read more

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.

Read more

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?

Read more

MD5 Collision

While I am reading back to my blog Generate MD5 Value from Big Data, I should have mention MD5 collision. This issue is within the standard algorithm not SQL Server itself. It happens rarely for character based hashing. It’s still a very reliable algorithm for change detecting but you need to be aware of this. … Read more

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.

Read more

DevTeach – Vancouver May 28th – Jun 1st 2012

DevTeach Vancouver May 28th – Jun 1st 2012

DevTeach DevTeach is the place you can learn many area’s of technologies from the experts. It will have pre-conference workshops (May 28th) , 3 full days of main conference (May 29,30,31) and a Post-Conference (June 1st). It’s not free but you will get what you pay for. Earlybirds’ discount is available. Check to you if you can find your favorate speakers there, and what they are going to talk about.

I will be presenting Parallel Task Scheduling with T-SQL, Code: 422, Level: 400. You might already attend my presentation on this before however this session will be the revised one with new features included. You won’t miss this enjoyable 75-minute session. See you there.

Read more

Are Jobs Currently Running?

Are jobs currently running? This question is asked very frequently by DBAs. The simplest answer is to use SSMS, navigate to SQL Server Agent, double click on Job Activity Monitor under job folder, then check Status column. It is ok if you have one or few servers. Under the situation where you have dozens, hundred, or thousands of SQL Server instances, the simple task described above would become to an extremely challenging work for human beings. The best way is to check them utilizing machine. Here I provide 2 ways to get job execution status programmatically and also tell you how to get the session id of a running job (if it’s running T-SQL Task)

Read more