Tuesday, July 16, 2013

26 THREADPOOL and SQL Server threads

Just like most of the Windows software, SQL Server is operating under thread model. This means the SQL Server process would spawn child threads to execute the work.

 

View the SQL Server thread

We can use sysinternal tools pstools or procexp to see the current SQL Server threads.

This is thread tab under the procexp

image

pslist.exe  sqlservr -d

image

Above is the screenshot of the output from pslist, it is about 49 threads under the SQL Server processes. However not all threads are “SQL Server worker threads” which are the actual threads that can work on the user requests.  We can use below query to check the internal sql server threads.

select COUNT(*) from sys.dm_os_workers

image

SQL Server would add the number of the threads when the workload is increasing .

The max number of the worker threads can be configured through “max worker threads” , the max_workers_count is the automatic max value base on the OS and CPU count for the SQL Servers.

In my test env, my current worker threads is 41 ( from dm_os_worker)  and max work threads is 512 . The minimum value from sp_configure does not really mean the minimum of the current value, it just mean the minimum value we can set for it. The current worker threads would increase over time till the max_workers_counts.

sp_configure "max worker threads"
select max_workers_count from sys.dm_os_sys_info;

image

Microsoft guild line for how to configure the max workthread can be found here : http://msdn.microsoft.com/en-us/library/ms190219.aspx

 

THREADPOOL exhaustion

Every user request would be assign to a work thread to carry out the work. If the request is in the pending state or waiting for other resources, the work thread still need to tied to the session until the resource become available. Since there are only limited number of the work threads, if all work threads has been used up, the SQL server would basically hang and can not accept new user connection. We will also see the error message like below:

2013-07-15 21:17:39.59 Server      New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 75%.

Here are the steps to reproduce the issue:

Create the test env

use master
CREATE DATABASE [THREADPOOL_DEMO] ON  PRIMARY 
( NAME = N'THREADPOOL_DATA', FILENAME = N'C:\DATA\THREADPOOL_DATA.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'THREADPOOL_LOG', FILENAME = N'C:\DATA\THREADPOOL_LOG.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);
 
use [THREADPOOL_DEMO];
create table t1 (c1 int,c2 varchar(50));
insert into  t1 values ( 1,'summer');
insert into  t1 values ( 2,'summer');

 

Run the DML to update the record but keep the transaction open

begin transaction ;
use [THREADPOOL_DEMO];
update t1 set c2='sunny' where c1=2;

Simulate 600 users connection to query the same table

ostress.exe -E -S. -Q"select * from THREADPOOL_DEMO.dbo.t1" -n600

SQL Server needs to hold exclusive lock on the t1 table and select would need to acquire the share lock. But share lock is not compatible with exclusive lock therefore all the query would be block and all the worker threads has been used out. No new connections can be accept to the sql server . The sql server becomes hang .  The existing connection would also hang because once the connection is idle, the worker threads would be assigned to other sessions hangs no worker threads would be available to execute the requests. 

The plists would also show 500+ threads

image

 

Diagnostic  

The only way to get into the SQL Server to do some troubleshooting is via DAC. ( sqlcmd –E –A )

Since SQL Server 2005, SQL Server reserve the dedicate scheduler for DAC .

select * from sys.dm_os_schedulers

image

-- show number of worker threads
select COUNT(*) from sys.dm_os_workers
-- show the THREADPOOL wait time
select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'
-- most of the sessions are waiting for LCK_M_S
select * from sys.dm_os_waiting_tasks 

image

identify the head block process

select top 1 session_id,blocking_session_id from sys.dm_os_waiting_tasks where session_id > 50
order by session_id 

image

Find the detail of the processes

select dec.session_id,des.login_name,des.login_time,des.host_name,des.program_name  ,TEXT from sys.dm_exec_connections dec
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) inner join sys.dm_exec_sessions des on
dec.session_id = des.session_id
where dec.session_id > 50 and dec.session_id = 52 
order by dec.session_id

image

Solution 1 : Kill the blocking spid

We can kill the head blocking spid from the DAC. Once the spid is gone. the other processes can acquire the share locks.

Solution 2 : Increase the max worker threads

“max worker threads”  is RR in 2008R2, so increase it would require SQL server bounce to make it effective. Base on Book on line, it seems to be dynamic in 2012. so it may be useful as the alternative than kill.

 

SOLUTION 3: set the database to READ_COMMITTED_SNAPSHOT

If you already know your application is unavoidable to have long transaction, then turn on the RCSI is the good alternative.  This needs to be done prior the transaction as it also require to obtain the lock on the database.

ALTER DATABASE THREADPOOL_DEMO SET READ_COMMITTED_SNAPSHOT ON

 

Reference

http://msdn.microsoft.com/en-us/library/ms190219.aspx

26 comments:

  1. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

    ReplyDelete
    Replies
    1. I am surprised why other specialized don’t perceive your site I’m greatly cheerful I discovered this.erp software dubai

      Delete
  2. Wonderful post! Thanks for taking time to share this with us. Keep us updated.

    Oracle Training in Chennai
    Oracle Training institute in chennai

    ReplyDelete
  3. A IEEE project is an interrelated arrangement of exercises, having a positive beginning and end point and bringing about an interesting result in Engineering Colleges for a particular asset assignment working under a triple limitation - time, cost and execution. Final Year Project Domains for CSE In Engineering Colleges, final year IEEE Project Management requires the utilization of abilities and information to arrange, plan, plan, direct, control, screen, and assess a final year project for cse. The utilization of Project Management to accomplish authoritative objectives has expanded quickly and many engineering colleges have reacted with final year IEEE projects Project Centers in Chennai for CSE to help students in learning these remarkable abilities.



    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai


    ReplyDelete


  4. 2 of 65
    comment

    Tuhin Pabna
    Attachments
    Nov 28, 2019, 9:29 AM
    to noyon0461


    2 Attachments

    keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore / https://www.excelr.com/digital-marketing-training-in-bangalore

    ReplyDelete
  5. I am overwhelmed by your post with such a nice topic. Usually I visit your blogs and get updated through the information you include but today’s blog would be the most appreciable. Well done!
    Please check this ExcelR Digital Marketing Course in Pune

    ReplyDelete
  6. Great Article. Thank you for sharing! Really an awesome post for every one.
    Digital Marketing Course In Kolkata
    Web Design Course In Kolkata

    ReplyDelete
  7. I wanted to inform you that you have people like me who appreciate your work. Definitely a great post. I would like to read this

    whizzherald.com
    talesbuzz
    disposable mobile numbers
    Gogoanime
    M4ufree

    ReplyDelete
  8. Very nice job... Thanks for sharing this amazing and educative blog post! ExcelR Digital Marketing Course Pune

    ReplyDelete
  9. I concur with a ton of the focuses you made in this article. I value the work you have placed into this and expectation you keep composing regarding this matter.
    Best Data Science training in Mumbai

    Data Science training in Mumbai


    ReplyDelete
  10. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    Data Science Certification in Bangalore

    ReplyDelete
  11. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete
  12. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    Data Science Course in Bangalore

    ReplyDelete
  13. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
    Data Science Training in Bangalore

    ReplyDelete
  14. The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

    Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


    Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

    ReplyDelete
  15. Fabulous blog found to be Very impressive and interesting to come across such an awesome blog. I would really thank the blogger to come up with the content which motivates the readers to be up to date with the fast growing technology in the current era. Once again nice blog keep it up and keep sharing the content as always.

    360DigiTMG Tableau Course

    ReplyDelete
  16. Mindblowing blog appreciating your endless efforts in developing a truly transparent content. Which probably the best one to come across disclosing the content which people might not aware of it. Thanks for bringing out the amazing content and keep sharing more further.

    360DigiTMG PMP Certification Course

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates