Karthick P.K on SQL Server

Archive for January, 2013

SQL Server Operating system (SOS) – Series 2

Posted by Karthick P.K on January 13, 2013

Context Switching:
When a thread is yielded from CPU windows stores the CONTEXT (current state such as CPU registers, program counters Etc) information of the current thread and loads the CONTEXT of the new thread which will run in the CPU.  
Why? A thread is yielded when executing an instruction, How the thread will resume from same point when it is rescheduled in CPU. Context information is stored while yielding and loaded when thread in rerun.
Note: yielding from CPU=Coming out of CPU

If there is high Context switching then system would spend more time on doing context switching than doing meaningful work.
When a thread moves out of CPU it is called as yielding.

When a thread can yield?
Thread which is running on CPU can yield out of the CPU under following key condition

Voluntary yield
Thread decides to yield by itself because of the logic in code executed by the thread. Generally a thread will voluntarily yield by calling Sleep(0) or SwitchToThread. When a thread voluntarily yields it is placed at the end of runnable list.
Ex: SQL Server thread will yield after sorting 64K of sort records.

Thread which is running on the CPU will be forced to yield from CPU when a thread with higher priority is ready to run. When a thread is preempted it is placed in the beginning of the runnable list. 

Quantum end
All the threads which is executed in operating system will get a time slice called as quantum. When a thread completes its quantum it is yielded and next thread is run. If there is no other thread is ready to run than thread run for another quantum.

Thread is terminated when it finishes execution and destroyed by calling TerminateThread

Thread and process priorities

windows supports thread priority level ranging from 0 (Lowest) to 31 (Highest). If all the threads have same priority they are scheduled in  round robin basis, but in reality threads running on OS will have different priorities. Among all the threads which can be run windows scheduler picks thread with highest priority to run first. Priority of a thread can be changed using WINAPI SetThreadPriority, Similarly priority of a process can be set while creating the process  (WINAPI CreateProcess  dwCreationFlags ) ,using WINAPI SetPriorityClass after the process is created and by using tools like task manager.

Let us attach the debugger to SQL Server process and see how to view the threads, thread stack and how SQL Server threads wait with out being scheduled.  

Download the windows debugger from below link

Windbg 32-bit package:

Windbg X64 package:


1. Start SQL Server in your test system.

2. Attach the debugger to SQL Server process. Refer below image. If you have more than one instance use the process id to attach with correct SQL Server process.



3. On command window type

.sympath srv*c:\Websymbols*;

4. Type .reload /f and hit enter. This will force debugger to immediately load all the symbols.

5. Verify if symbols are loaded for  SQL Server by using the debugger command lmvm

lmvm sqlservr

6. Type

!peb  to display the information about process from process environment block.

7.  Type ~ to display all the threads in the process. First column in the output represents thread ID.

8. To  look at the stack of a specific thread,switch to the thread using thread ordinal (or) thread ID.

Debugger command ~ displays all the thread’s of the process.

Thread ordinal: is decimal value used by debugger to identify the thread starts from 0 (First columns in below output).

Thread ID : Is the ID assigned to each thread by operating to system. You can switch to a thread using thread ID by debugger command ~~[ThreadID]s     (4th column in ~ output )

In the below image I have printed all the thread’s and stack of thread ordinal 8 which is scheduler monitor thread.



9. Type  g in command prompt to resume the process.

10. Connect to SQL Server from management studio. Run select * from sysprocesses. All the sessions which has a non zero value for KPID has a valid windows thread associated with the session. When executing queries which choose parallel plan there will be more than one row for same session and each rows will have different KPID.

11. To look at the thread stack of a session which is currently executing a task or background process . Convert KPID value associated with session in sysprocess in Hexadecimal value and type below command window of debugger.

~~[Hex value of a thread]s

Type kC

12.  Let us create a small blocking scenario to understand how threads wait in SQL Server.


create table a (A int)
insert into a  values (1);
begin transaction
update a set A =1+1


select * from a

Session-2 will be blocked. Look at the stack of blocked thread

13. Run select * from sysprocesses where blocked<>0

Identify the KPID of the session which is blocked.

14. Convert KPID in to hexadecimal value

15. Break the debugger to execute the debugger commands (CTRL+B) or 7th Icon in menu bar.

16. Look at the stack of the thread which is waiting for lock (Blocked) by using the Hex value of KPID

~~[Hex value of KPID]s




17. Above thread from second session which we created is waiting for an event using WaitForsingleObject. When the first session releases the lock this thread will be signaled and resumes execution.

We will see the details about WaitForsingleObject ,Waitformultipleobjects, Event (Manual auto reset) etc. in more details in forthcoming blogs

If you liked this post do like us on Facebook at and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.


Posted in SQL Server Engine, SQLServer SOS | Tagged: , , , , , , , , | 2 Comments »

SQL Server Operating system (SOS) – Series 1

Posted by Karthick P.K on January 10, 2013

Before we start studying SQLOS we will recollect some of the basic OS concepts. 

What is process?

Process is instance of service or application which is running. Each process will have address space that contains all the Executable, Dlls, data, thread stacks etc.  Operating system maintains a kernel objects for each process to manage the process. One or more threads which runs under the context of the process and execute the code in address space. Each thread can execute the code and maintains its own set of CPU registers and stack. When a process is created primary thread of the process is created and starts executing the main() or Other similar function.  Primary thread can create additional threads using create thread function and lpStartAddress (Thread entry point ) defines the function that is to be executed by the thread which is created.



Ex: Ureadfile will be the thread entry point for the new thread which is created using below code.

CreateThread(0,0,(LPTHREAD_START_ROUTINE  )Ureadfile,(LPVOID)&PSUreadfile[i],  0,  NULL);



What is thread?

Threads executes the code in process.  There can be one  (Single threaded ) or more( Multi-threaded) threads for every process. In multi-threaded applications each thread has to synchronize their activities among other threads. Ex: Allowing one thread to modify a Global while other is reading it can cause race conditions.

SQL Server uses synchronization techniques like Spinlocks, Latches, Events Etc.


Threads states

Threads can be in below core states (There are other states which we will discuss on need)



Wait state represents thread is waiting for some resource. A thread in this state is not eligible to be scheduled from OS.

SQL Server threads can be in wait state in multiple places. A thread requesting for lock has to wait till it is available and goes for sleep unless signaled when the lock is available.

A thread can call WaitForSingleObject  and wait without competing  CPU resource



LMHandle = CreateMemoryResourceNotification(LowMemoryResourceNotification);

WaitForSingleObject( LMHandle,INFINITE);

In above example Thread will wait till there is Lowmemoryresourcenotification from windows.


Thread is running in CPU.



Thread is ready to run and waiting for its CPU slice. In SQL Server thread which are ready to run on scheduler will stay in runnable list of scheduler till they get chance to run on scheduler.

All the threads which is executed in operating system will get a time slice to run in CPU called as quantum.Thread is yielded from scheduler after its quantum is completed.




Preemptive scheduling:

Operating system can interrupt the thread execution any time. OS can halt the thread execution and schedule another thread to run at any time.


Non-Preemptive scheduling:

Operating system cannot interrupt the thread execution any time. The worker owns the scheduler until it yields to another worker on the same CPU. If the thread which runs on CPU(Scheduler) don’t yield in time it monopolizes the CPU until it finishes. 

Windows 3.x and DOS were using Non-Preemptive scheduling. In Non-Preemptive scheduling context switching is generally reduced because the operating system does not interrupt code execution and It is easier to implement a multi-threaded application in Non-Preemptive mode because synchronization may be less of an issue.  A bad application can easily ‘hang’ the entire system if thread from application does not yield from CPU allowing other applications threads to execute.


If you liked this post do like us on Facebook at and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in SQL Server Engine, SQLServer SOS | Tagged: , , , , , , | 2 Comments »

SQL Server fails to start with error "Failed allocate pages: FAIL_PAGE_ALLOCATION 1" During startup

Posted by Karthick P.K on January 6, 2013

SQL Server fails to start and If you look at the SQL Server Error log you will find "Failed allocate pages: FAIL_PAGE_ALLOCATION" and SQL Server generating exception dump. Similar to the SQL Server error log below.


Note: This blog is applicable when you out get of memory error during startup (or) with event ID: 2019 in system event log. For general troubleshooting of SQL Server out of memory errors follow steps in Troubleshooting SQLServer Memory


2013-01-02 12:31:20.91 Server      Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86)

                Jun 17 2011 00:57:23

                Copyright (c) Microsoft Corporation

                Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

2013-01-02 12:31:20.91 Server      (c) Microsoft Corporation.

2013-01-02 12:31:20.91 Server      All rights reserved.

2013-01-02 12:31:20.91 Server      Server process ID is 1583.

2013-01-02 12:31:20.91 Server      Authentication mode is MIXED.

2013-01-02 12:31:20.91 Server      Logging SQL Server messages in file ‘C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG’.

2013-01-02 12:31:20.91 Server      This instance of SQL Server last reported using a process ID of 9240 at 1/3/2013 7:31:20 PM (local) 1/4/2013 12:31:20 AM (UTC). This is an informational message only; no user action is required.

2013-01-02 12:31:20.91 Server      Registry startup parameters:

                 -d C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\master.mdf

                -e C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\Log\ERRORLOG

                -l C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\DATA\mastlog.ldf

2013-01-02 12:31:20.92 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2013-01-02 12:31:20.92 Server      Detected 24 CPUs. This is an informational message; no user action is required.

2013-01-02 12:31:20.94 Server      Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

2013-01-02 12:31:27.33 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1

2013-01-02 12:31:27.33 Server     

Memory Manager                                   KB

—————————————- ———-

VM Reserved                                 1534584

VM Committed                                  51576

AWE Allocated                                     0

Reserved Memory                                1024

Reserved Memory In Use                            0

2013-01-02 12:31:27.33 Server      Error: 17311, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

2013-01-02 12:31:27.33 Server      Using ‘dbghelp.dll’ version ‘4.0.5’

2013-01-02 12:31:27.34 Server      **Dump thread – spid = 0, EC = 0x00000000

2013-01-02 12:31:27.34 Server      ***Stack Dump being sent to C:\Microsoft SQL Server\MSSQL10_50.MSSQLWIKIServer\MSSQL\LOG\SQLDump0008.txt

2013-01-02 12:31:27.34 Server      * *******************************************************************************

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * BEGIN STACK DUMP:

2013-01-02 12:31:27.34 Server      *   01/03/13 19:31:27 spid 4344

2013-01-02 12:31:27.34 Server      *

2013-01-02 12:31:27.34 Server      * ex_handle_except encountered exception C0000005 – Server terminating





Why would SQL Server fail with out of memory error (FAIL_PAGE_ALLOCATION)during the startup? Only possible reason that I could think of is Paged or NonPaged pool is empty.

How to prove if my Paged / NonPaged pool is empty?  Look at the system event log for the Event ID: 2019


You will find error in system event log similar to one you see below.


Event Type:        Error

Event Source:    Srv

Event Category:                None

Event ID:              2019

Date:                     2013-01-02

Time:                     12:31:00 PM

User:                     N/A

Computer:          MSSQLWIKIServer


The server was unable to allocate from the system nonpaged pool because the pool was empty.



Above error indicates nonpaged pool is empty, When Nonpaged pool is empty every application would fail. How to identify who is consuming Nonpaged pool?


Use poolmon.exe from windows support tools. (Steps are documented in This KB).

If you r OS is windows 2003 or above you can simple run the exe from command prompt and identify who is consuming (Leaking J) space in Paged / NonPaged pool.

Below is sample output of poolmon.exe which I collected from my test system




Memory consumption by each tag is printed in above output. After finding the tag which is leaking the memory (Highest bytes)identify the Driver which is using the tag by using  find command or strings utility from sysinternals (search for TAG in drivers folder %Systemroot%\System32\Drivers). Once you identify the driver, check if there are any known issue with the driver or you may have to contact the vendor of the driver to identify why the driver is consuming large amount of pooled /Non-pooled memory.


If you liked this post, do like us on Facebook at , join our Facebook group MSSQLWIKI and post your SQL Server questions to get answered by experts.



Thank you,

Karthick P.K |

 My Facebook Page |My Site| Blog space| Twitter



The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.















Posted in Configuration, Memory, SQL Server Engine, SQL Server memory, Startup failures | Tagged: , , , , | 26 Comments »

%d bloggers like this: