Tuesday, July 16, 2013

113 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

113 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
  17. Thanks for sharing this, I actually appreciate you taking the time to share with everybody.
    Data Science Training In Hyderabad

    ReplyDelete
  18. 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.

    ReplyDelete
  19. Montre homme pas cher sur l' pas cher homme montre Une grande sélection de montres pour homme vraiment à petit prix.

    ReplyDelete
  20. Discover 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.

    ReplyDelete
  21. Great Article. Thank you for sharing! Really an awesome post data science course in Hyderabad

    ReplyDelete
  22. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic.

    Big Data Training Institute in Pune
    Hadoop Training in Pune

    ReplyDelete
  23. 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
    data science course in Hyderabad

    ReplyDelete
  24. 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!
    data science training

    ReplyDelete
  25. 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..
    data science course in noida

    ReplyDelete
  26. 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

    ReplyDelete
  27. If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries
    Data Science Training in Hyderabad

    ReplyDelete
  28. As always your articles do inspire me. Every single detail you have posted was great.
    data scientist certification

    ReplyDelete
  29. Your content is very unique and understandable useful for the readers keep update more article like this.
    data science course in delhi

    ReplyDelete
  30. 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
    data science course delhi

    ReplyDelete
  31. Thanks for posting the best information and the blog is very informative.Data science course in Faridabad

    ReplyDelete
  32. Thank you for this blog. I have read this and it gives more useful info to me.
    statistics for data science tutorial
    hacking tutorial tips and tricks

    ReplyDelete
  33. 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.
    Digital Marketing training

    ReplyDelete

  34. I see the greatest contents on your blog and I extremely love reading them.

    Best Institute for Data Science in Hyderabad

    ReplyDelete
  35. I must appreciate you for providing such valuable content for us. To make our dreams a reality, Advancements in the field of digital technology means machine learning and artificial intelligence are becoming more important each passing day. Most of the top IT and research companies are now seeking specialists in artificial intelligence. With the scope for artificial intelligence significantly expanding, career in AI are becoming aplenty.

    ReplyDelete
  36. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Digital Marketing Course

    ReplyDelete
  37. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    Digital Marketing Course in Bhilai

    ReplyDelete
  38. Amazing Article ! I would like to say thank you for the efforts you had made for writing this awesome article. This article inspired me to read more your blogs. keep it up.

    Affiliate Marketing Training In Telugu
    Affiliate Marketing Means In Telugu
    Digital Marketing Training In Telugu
    Blogging In Telugu
    Podcast Meaning In telugu
    SEO Meaning In Telugu
    1000 Social BookMarking Sites List

    ReplyDelete
  39. Thanks for posting the best information and the blog is very helpful.digital marketing institute in hyderabad

    ReplyDelete
  40. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  41. great article!! sharing these type of articles is the nice one and i hope you will share an article on data science.By giving a institute like 360DigiTMG.it is one the best institute for doing certified courses
    data science training in pune

    ReplyDelete
  42. I think this is a really good article. You make this information interesting and engaging. You give readers a lot to think about and I appreciate that kind of writing.
    data scientist training and placement in hyderabad

    ReplyDelete
  43. If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries

    digital marketing courses in hyderabad with placement

    ReplyDelete
  44. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  45. This is an excellent post . thanks for sharing it. It is really what I wanted to see. I hope in the future you will continue to share such an excellent post.
    business analytics course

    ReplyDelete
  46. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  47. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.
    Business Analytics Course

    ReplyDelete
  48. Thanks for posting the best information and the blog is very good.digital marketing institute in hyderabad

    ReplyDelete
  49. I will very much appreciate the writer's choice for choosing this excellent article suitable for my topic. Here is a detailed description of the topic of the article that helped me the most.
    Best Data Science Courses in Bangalore

    ReplyDelete
  50. I am very happy to have seen your website and hope you have so many entertaining times reading here. Thanks again for all the details.
    Data Analytics Course in Bangalore

    ReplyDelete
  51. With so many books and articles appearing to usher in the field of making money online and further confusing the reader on the real way to make money.
    Digital Marketing Course in Bangalore

    ReplyDelete
  52. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Best Data Science courses in Hyderabad

    ReplyDelete
  53. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  54. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  55. Thanks for posting the best information and the blog is very important.digital marketing institute in hyderabad

    ReplyDelete
  56. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    best data science institute in hyderabad

    ReplyDelete
  57. Thanks for posting the best information and the blog is very important.data science institutes in hyderabad

    ReplyDelete
  58. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete
  59. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    artificial intelligence training in chennai

    ReplyDelete
  60. I just found this blog and have high hopes for it to continue. Keep up the great work, its hard to find good ones. I have added to my favorites. Thank You.
    business analytics courses aurangabad

    ReplyDelete
  61. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    artificial intellingence training in chennai

    ReplyDelete
  62. The writer is enthusiastic about purchasing wooden furniture on the web and his exploration about best wooden furniture has brought about the arrangement of this article.
    data scientist course in hyderabad

    ReplyDelete
  63. Honestly speaking this blog is absolutely amazing in learning the subject that is building up the knowledge of every individual and enlarging to develop the skills which can be applied in to practical one. Finally, thanking the blogger to launch more further too.

    data science course in bangalore with placement

    ReplyDelete
  64. Fantastic article I ought to say and thanks to the info. Instruction is absolutely a sticky topic. But remains one of the top issues of the time. I love your article and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  65. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
    Data Science certification Course in Bangalore

    ReplyDelete
  66. Thanks for posting the best information and the blog is very important.digital marketing institute in hyderabad

    ReplyDelete
  67. This is really very nice post you shared, i like the post, thanks for sharing..
    business analytics course

    ReplyDelete
  68. Nice to be seeing your site once again, it's been weeks for me. This article which ive been waited for so long. I need this guide to complete my mission inside the school, and it's same issue together along with your essay. Thanks, pleasant share.
    Data Science training in Bangalore

    ReplyDelete
  69. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .
    Data Science certification training in Bangalore

    ReplyDelete
  70. i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
    artificial intelligence training in chennai

    ReplyDelete
  71. Thanks for posting the best information and the blog is very important.artificial intelligence course in hyderabad

    ReplyDelete
  72. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
    Data Analytics Course in Bangalore

    ReplyDelete
  73. Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .
    Data Analytics training in Bangalore

    ReplyDelete
  74. this is really nice to read..informative post is very good to read..thanks a lot!
    business analytics course

    ReplyDelete
  75. Good to be here in your article or post, whatever, I think I should also work hard for my own website like I see some good and updated working in your site.
    data scientist course in hyderabad

    ReplyDelete
  76. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    business analytics course

    ReplyDelete
  77. I loved the content very much. Thanks for sharing

    Trading for beginners

    ReplyDelete
  78. Tremendous blog quite easy to grasp the subject since the content is very simple to understand. Obviously, this helps the participants to engage themselves in to the subject without much difficulty. Hope you further educate the readers in the same manner and keep sharing the content as always you do.

    data science course in faridabad

    ReplyDelete
  79. Thanks for a marvelous posting! I actually enjoyed reading it, you might be
    a great author.I will be sure to bookmark your blog and will
    often come back at some point. I want to encourage yourself to continue your great posts,
    have a nice day!

    my web page;오피

    ReplyDelete
  80. Really Nice Information It's Very Helpful All courses Checkout Here.
    data science course aurangabad

    ReplyDelete
  81. It is late to find this act. At least one should be familiar with the fact that such events exist. I agree with your blog and will come back to inspect it further in the future, so keep your performance going.

    Business Analytics Course

    ReplyDelete
  82. I have voiced some of the posts on your website now, and I really like your blogging style. I added it to my list of favorite blogging sites and will be back soon ...

    Best Data Science Courses in Bangalore

    ReplyDelete
  83. I bookmarked your website because this site contains valuable information. I am very satisfied with the quality and the presentation of the articles. Thank you so much for saving great things. I am very grateful for this site.
    Data Analytics Course in Bangalore

    ReplyDelete
  84. I will very much appreciate the writer's choice for choosing this excellent article suitable for my topic. Here is a detailed description of the topic of the article that helped me the most.
    Digital Marketing Course in Bangalore

    ReplyDelete
  85. Really Nice Information It's Very Helpful All courses Checkout Here.
    ai courses aurangabad

    ReplyDelete
  86. Thanks for posting the best information and the blog is very good.data science course in Lucknow

    ReplyDelete
  87. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Data Science Certification in Bhilai

    ReplyDelete
  88. Extraordinary blog went amazed with the content that they have developed in a very descriptive manner. This type of content surely ensures the participants to explore themselves. Hope you deliver the same near the future as well. Gratitude to the blogger for the efforts.

    Data Science Training

    ReplyDelete

 

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