Blog Archives

SQL Server thread scheduling cycle

Here is a simple diagram illustrating how SQL Server thread scheduling works.

SQL Server Thread cycle

Whenever a new thread arrives it got a slice of CPU. This is shown as Running.

When the thread has to wait for a resource it moves to Suspended list.

When the thread in the Suspended list is notified for the available resources, the thread moves to Runnable queue.

Meanwhile the next thread in Runnable queue will move to Running for it’s share of CPU.

This cycle goes on till the thread finishes the work.

The time taken by a thread between leaving the Running state and again enters Running state is called Wait Time.

The time taken by a thread between leaving the Suspended list and entering the Running state (i.e. time spend in Runnable queue) is called Signal Wait Time.

You can see these states when you query the DMV sys.dm_exec_cached_plans on status column or executing  sp_who

The Wait time is a very important factor in SQL Server troubleshooting. You can read more about in SQL Server 2005 Performance tuning using Waits and Queues.

Leave a comment

August 1, 2014 · 4:03 PM

Jim Gray’s analogy on Database Logging

Jim Gray was a computer scientist and Turing Award recipient pioneered in database and transaction processing system. His major contributions are implemented in major relational database systems as logging, locking, and he proposed the ACID properties of a transactional database system (though the acronym is coined by Andreas Reuter and Theo Härder in 1983) to name a few.

Hansel & Gretel and breadcrumb

He presented a paper in Proceedings of Seventh International Conference on Very Large Databases, in 1981, titled ‘The Transaction Concept: Virtues and Limitations’. In this paper he explained the concept of database transactions and different methods to implement it. His analogy on transaction logging is very interesting to read and help to understand the concept clearly and easily. This is as follows.

The legendary Greeks, Ariadne and Theseus, invented logging. Ariadne gave Theseus a magic ball of string which he unraveled as he searched the Labyrinth for the Minotaur. Having slain the Minotaur, Theseus followed the string back to the entrance rather then remaining lost in the Labyrinth. This string was his log allowing him to undo the process of entering the Labyrinth. But the Minotaur was not a protected object so its death was not undone by Theseus’ exit.

Hansel and Gretel copied Theseus’ trick as they wandered into the woods in search of berries. They left behind a trail of crumbs that would allow them to retrace their steps by following the trail backwards, and would allow their parents to find them by following the trail forwards. This was the first undo and redo log. Unfortunately, a bird ate the crumbs and caused the first log failure.”

Leave a comment

July 23, 2014 · 12:59 PM