Month: April 2021

MSSQL SERVER: Schedulers and Threads

Microsoft SQL Server is a Service that runs inside of an OS (no longer exclusively on Windows – i.e. SQL Server on Linux). However, it has its own mechanisms for handling memory management, IO and thread scheduling. This is done through access to the host OS API in most cases, but SQL is still the one actively managing these resources.

Thread

 A thread is the basic unit to which the operating system allocates processor time. A thread can execute any part of the process code, including parts currently being executed by another thread. Threads are the smallest units of processing that can be executed by an operating system, and allow the application logic to be separated into several concurrent execution paths.

SQL Server Worker Thread

Also known as worker or thread, is a logical representation of an operating system thread. When executing serial requests, the SQL Server Database Engine will spawn a worker to execute the active task (1:1).

Process

When an operating system executes an instance of an application, it creates a unit called a process to manage the instance. The process has a thread of execution. This is the series of programming instructions performed by the application code.

Request

In the scope of SQL Server, a request is the logical representation of a query or batch. A request also represents operations required by system threads, such as checkpoint or log writer. Requests exist in various states throughout their lifetime and can accumulate waits when resources required to execute the request are not available, such as locks or latches.

Task

A task represents the unit of work that needs to be completed to fulfill the request. One or more tasks can be assigned to a single request.

Scheduler

A scheduler, also known as SOS scheduler (meaning SQL OS scheduler but never actually referred to as such), manages worker threads that require processing time to carry out work on behalf of tasks.

Quantum

The amount of time that a thread is permitted to run on the processor before it must yield to another thread and return to the Runnable Queue or the Wait List.

Scheduler Components

At a high and somewhat abstracted level, schedulers have three main components: the Processor, Waiter List and Runnable Queue.

The Processor is the logical processor core which processes the threads one at a time. Each scheduler is mapped to a single processor.

The Waiter List represents the list of threads that are waiting for resources (such as latches and locks).

The Runnable Queue represents the group of threads that are not waiting on resources (as they have already acquired them) and are instead are waiting for their turn to run on a processor.

The thread of a process will move between these three states as they get executed. The threads are always either doing work on the processor, waiting for a particular resource, or they have a resource and are waiting in line to run on the  processor again. Once a thread is running on the processor, it will continue to do so until either it has finished its work and needs to again wait for another resource OR it reaches its maximum allotted time on the processor (called the OS quantum). This Quantum value is 4ms is non configurable.

Thread States

At any given time a thread can be in one of three states: RUNNING, SUSPENDED, or RUNNABLE.

RUNNING is the state where the thread is actually executing on the processor and utilizing CPU spins. Only one thread per scheduler can have this state at a time.

During execution, if the thread needs to wait for a particular resource in SQL (such as a lock or latch), it yields its time on the processor and moves to the Waiter List. When here the state of the thread changes to SUSPENDED while it waits for the resource to be acquired.

Once the resource has been acquired the thread is again ready to use the processor again but must wait its turn behind the other threads waiting to execute so it moves to the Runnable Queue and its state is changed to RUNNABLE.

NOTE:

If a RUNNING thread reaches the end of its 4ms quantum and doesn't need to wait to acquire another resource, voluntarily yields for the next thread and enters the Runnable Queue where its state changes back to RUNNABLE.

This dance continues until the thread(s) complete all the work assigned to them. This is called cooperative or non-preemptive scheduling. It relies on all of the processes at play voluntarily yielding when they are supposed to. For a comparison see this post on Preemptive vs Cooperative Multitasking.

MSSQL SERVER: Create an Extended Event Session with a “NOT LIKE” filter criteria on sql_statement_completed

As of the writing of this post, when creating extended events sessions in SQL Server Management Studio (SSMS), on the Set Session Event Filters page of the New Session Wizard, there is an option to include events that are like a specified value (using the like_i_sql_unicode_string operator) but there is not currently anything in the GUI to EXCLUDE queries that meet this filter criteria.

Adding a “NOT LIKE” clause to the extended events filter seems to be a limitation of the GUI currently. The steps below show you how to select all of your extended events via the wizard and then add the “NOT LIKE” functionality at the end by scripting out the Extended Event and making a minor tweak.

Open the New Session Wizard in SSMS:

Give it a name

Select “Do not use a template” and click Next

Search for sql_statement_completed in the list and add it to the Selected Events and click Next

On the Capture Global Fields page, click Next

On the Set Session Event Filters page, select “sqlserver.sql_text from the “Field” drop down list and set the “Operator” to like_i_sql_unicode_string and set the “value” field to the full or partial sql query that you would like to exclude. Then click next.

Continue to the Summary page of the wizard and click on the “Script” button

You should see a script like this:

We’re almost there but this script in its current form will do the opposite of what we want. This script will capture ONLY the sp_BlitzFirst execution. To have it exclude these executions, we have to make the following change:

Here is the TSQL text from this proof of concept:

CREATE EVENT SESSION [XEventFilterTest] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

    WHERE ( NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'EXEC sp_BlitzFirst%')))

WITH (STARTUP_STATE=OFF)

GO

Other Notes:

The total number of characters allowed for ALL filters on an event is 3,000. If you execeed this threshold, you may see an error that resembles the following:

Msg 25716, Level 16, State 1, Line 1
The predicate on event, "sqlserver.attention", exceeds the maximum length of 3000 characters.

To workaround this issue, split your Extended Events Session into multiple sessions.

MSSQL SERVER: ERROR: CREATE TABLE permission denied in database ‘tempdb’

I was recently working with a client that was periodically but inconsistently getting a strange error when one of their SQL Server Agent jobs ran:

ERROR: CREATE TABLE permission denied in database 'tempdb'

Why is this error confusing? Well, at a minimum every database and every user has at least the public role. The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default.

We also know that any user with the public role can create temporary tables in tempdb. So, how on earth does this error make any sense?

Let me demonstrate.

It is still true that with only the public role a user can create both local and global temporary tables.

If you want to demonstrate this, create a new user in SQL and make sure that it only has the public role then try to create some tables like below:

Local temp table:

CREATE TABLE #test_table_local (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

Global temp table:

CREATE TABLE ##test_table_global (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

But watch what happens we we try to create a regular “permanent” table in tempdb:

CREATE TABLE test_table_permanent (
     product_name VARCHAR(MAX),
     list_price DEC(10,2)
 );

So, as you can see, the original statement is still true. The public role allows users to create temp tables in tempdb without any additional permissions. However, to create permanent tables in tempdb you will still need the CREATE TABLE permission.

In my client’s case it was a matter of a simple typo. One particular code path in the SQL Agent job triggered a code path containing the typo where a CREATE TABLE statement was missing a ‘#’ in front of the table name.

If you’re wondering why you would create a “permanent” table in tempdb, you wouldn’t in most cases but it does have some unique properties in that the table is available from other sessions (unlike local temporary tables), and it is not deleted automatically even if there are no connections to it (unlike global temporary tables). However, it does still get deleted when the instance is restarted (just like everything else in tempdb).

Reference Links & More Information

Server and Database Roles in SQL Server | The Public Role