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
pslist.exe sqlservr -d
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
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;
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
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
-- 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
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
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
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
Great Explanation and more useful for anyone.Thanks for sharing...
ReplyDeleteoracle course in chennai
I am surprised why other specialized don’t perceive your site I’m greatly cheerful I discovered this.erp software dubai
DeleteWonderful post! Thanks for taking time to share this with us. Keep us updated.
ReplyDeleteOracle Training in Chennai
Oracle Training institute in chennai
I am very much satisfied with your blog, thanks for updating.
ReplyDeleteHadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
German Classes in chennai
web designing course in chennai
web designing course in Anna Nagar
Thanks for sharing this blog.
ReplyDeleteweb development classes | web designing and development course
php developer course | php course in chennai
magento course in chennai | magento developer training
Thank you for your guidance to with upgrade information. Keep posting...!
ReplyDeletePega Training in Chennai
Pega Developer Training
Advanced Excel Training in Chennai
Linux Training in Chennai
Power BI Training in Chennai
Tableau Training in Chennai
Job Openings in Chennai
Oracle Training in Chennai
Oracle DBA Training in Chennai
Social Media Marketing Courses in Chennai
Spark Training in Chennai
Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
ReplyDeleteWeb Designing Training Institute in Chennai | web design training center in chennai | web designing course in chennai with placement
Mobile Application Development Courses in chennai
Data Science Training in Chennai | Data Science courses in Chennai
Professional packers and movers in chennai | PDY Packers | Household Goods Shifting
Web Designing Training Institute in Chennai | Web Designing courses in Chennai
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.
ReplyDeleteSpring 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
ReplyDelete2 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
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!
ReplyDeletePlease check this ExcelR Digital Marketing Course in Pune
Great Article. Thank you for sharing! Really an awesome post for every one.
ReplyDeleteDigital Marketing Course In Kolkata
Web Design Course In Kolkata
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
ReplyDeletewhizzherald.com
talesbuzz
disposable mobile numbers
Gogoanime
M4ufree
Best Ps1 Games
ReplyDeleteBest PSP Games
Best PSP Games
Best NES Games
Best Super Nintendo Games
Very nice job... Thanks for sharing this amazing and educative blog post! ExcelR Digital Marketing Course Pune
ReplyDeleteI 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.
ReplyDeleteBest Data Science training in Mumbai
Data Science training in Mumbai
It is actually a great and helpful piece of information. I am satisfied that you simply shared this helpful information with us. Please stay us informed like this. Thanks for sharing.
ReplyDeleteDigital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery
I liked the way you posted here. Thankful for the presentation. Keep it up nice page
ReplyDeleteAi & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
I thank you for the information and articles you provided cara menggugurkan kandungan dan mempercepat haid
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI 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.
ReplyDeleteData Science Certification in Bangalore
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.
ReplyDeleteProjects 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.
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.
ReplyDeleteData Science Course in Bangalore
I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
ReplyDeleteData Science Training in Bangalore
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.
ReplyDeleteProjects 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.
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.
ReplyDelete360DigiTMG Tableau Course
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.
ReplyDelete360DigiTMG PMP Certification Course
Thanks for sharing this, I actually appreciate you taking the time to share with everybody.
ReplyDeleteData Science Training In Hyderabad
Breitling replica watches is one of the world's most respected Swiss horologists. replica breitling Founded in 1884 in Saint-Imier, the brand cemented its reputation as an aviation specialist, but is now an authority in diving and chronograph categories, too. Specialist watchmaking All COSC certified chronometers.
ReplyDeleteMontre homme pas cher sur l' pas cher homme montre Une grande sélection de montres pour homme vraiment à petit prix.
ReplyDeleteDiscover Moncler's fake moncler jackets collection of puffer jackets and clothes for men,fake moncler women jackets women and kids, merging fashion with high performance fabrics.
ReplyDeleteGreat Article. Thank you for sharing! Really an awesome post data science course in Hyderabad
ReplyDeleteThis information is impressive..I am inspired with your post writing style & how continuously you describe this topic.
ReplyDeleteBig Data Training Institute in Pune
Hadoop Training in Pune
keep up the good work. this is an Ossam post. This is to helpful, i have read here all post. i am impressed. thank you. this is our site please visit to know more information
ReplyDeletedata science course in Hyderabad
I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it!
ReplyDeletedata science training
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
ReplyDeletedata science course in noida
This information is meaningful and magnificent which you have shared here about theWomen Designer Jacket. I am impressed by the details that you have shared in this post and It reveals how nicely you understand this subject. I would like to thanks for sharing this article here.Women Designer Jacket for Sale Online
ReplyDeleteIf you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries
ReplyDeleteData Science Training in Hyderabad
As always your articles do inspire me. Every single detail you have posted was great.
ReplyDeletedata scientist certification
Your content is very unique and understandable useful for the readers keep update more article like this.
ReplyDeletedata science course in delhi
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ
vé máy bay từ seattle về việt nam
Vé máy bay từ Nhật Bản về Việt Nam
từ canada về việt nam quá cảnh ở đâu
Hello there to everyone, here everybody is sharing such information, so it's fussy to see this webpage, and I used to visit this blog day by day
ReplyDeletedata science course delhi
Thanks for posting the best information and the blog is very informative.Data science course in Faridabad
ReplyDeleteThank you for this blog. I have read this and it gives more useful info to me.
ReplyDeletestatistics for data science tutorial
hacking tutorial tips and tricks
Truly incredible blog found to be very impressive due to which the learners who ever go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such an phenomenal content. Hope you arrive with the similar content in future as well.
ReplyDeleteDigital Marketing training