none
Will OPENXML induces high THREADPOOL wait? RRS feed

  • Question

  • My developers love to write stored procedure with string parameter which essentially is XML value, and use OPENXML to extract data from it. Now our production database gets high THREADPOOL wait. Is it OPENXML sprawns an extra worker thread?
    Monday, June 3, 2019 11:21 AM

Answers

  • Hi nonno,

    What is your version of SQL Server?

    OPENXML is circa SQL Server 2000 era.

    XML has its merits:

    • SQL Server supports native XML data type
    • Hierarchical structures
    • Validation of stored procedures parameters against XML Schema (XSD)
    • XQuery language
    • etc.

    Please share sample of input XML and SQL that inserts it into a table.

    Here is an alternative method how to handle XML parameters in stored procedures:

    DECLARE @xml XML = '<Tickets>
       <Ticket>
          <BetType>0</BetType>
          <BetAmount>100</BetAmount>
    	  <PendingAmount>700</PendingAmount>
    	  <Remark>Some remark</Remark>
       </Ticket>
       <Ticket>
          <BetType>1</BetType>
          <BetAmount>200</BetAmount>
    	  <PendingAmount>780</PendingAmount>
    	  <Remark>Another remark</Remark>
       </Ticket>
    </Tickets>';
    
    -- INSERT INTO #TmpTickets -- uncomment when you are ready
    SELECT col.value('(BetType/text())[1]','TINYINT') AS BetType
        , col.value('(BetAmount/text())[1]','BIGINT') AS BetAmount
        , col.value('(PendingAmount/text())[1]','BIGINT') AS PendingAmount
        , col.value('(Remark/text())[1]','VARCHAR(1024)') AS Remark
    FROM @xml.nodes('/Tickets/Ticket') AS tab(col);


    • Edited by Yitzhak Khabinsky Monday, June 3, 2019 11:38 PM
    • Marked as answer by nonno Tuesday, June 4, 2019 9:53 AM
    Monday, June 3, 2019 12:48 PM
  • Or can it be solved by increasing the max worker threads server option? It's 2008R2 enterprise 64 bits, 32x logical CPUs. According to BOL, it only has 960 worker threads.

    Maybe. If the problem is a misbehaving application that does not close threads, it will not.

    If the application really needs those threads, maybe. But I believe you can get other nasty problems.

    What is the setting for max degree of parallelism? If it's 0, set it to 4 or 8. That will help if the issue that too many processes want to run parallel queries.

    Replacing OPENXML with XQuery is long overdue as suggested by Yitzhak. Nevertheless, if there are a lot of places where OPENXML is used, it will take time and require thorough testing. And it may not solve the problem at all.

    In essence, the correct approach to solve a problem like this is not to change settings blindly, but first make an analysis of why it happens in the first place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by nonno Tuesday, June 4, 2019 9:47 AM
    Tuesday, June 4, 2019 9:16 AM

All replies

  • I could be wrong but I don't think the cause of the TREADPOOL waits is extra OPENXML threads. The more likely reason is costly XML parsing and associated queries (e.g. parallel queries that join to the XML data) that tie up worker threads for long periods. Check the execution plans to see of this is the culprit.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 3, 2019 11:28 AM
  • We use SentryOne to monitor our production system. It points out INSERT #tempTable SELECT FROM OPENXML slow, as illustrated below. Actually the xml data is small, so I think xml parsing shouldn't very costly.

    High THREADPOOL wait at that moment:

    • Edited by nonno Monday, June 3, 2019 12:12 PM
    Monday, June 3, 2019 11:48 AM
  • I agree the insert here doesn't look to be costly but the 11+ second duration is concerning. What is the wait type contributing to the duration?

     

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 3, 2019 11:59 AM
  • THREADPOOL wait was very high. It's a production database, so there are many concurrent requests. But I wonder why only that INSERT FROM OPENXML query being stuck, and it just happens occasionally, but more frequent during peak hour.

    • Edited by nonno Monday, June 3, 2019 12:16 PM
    Monday, June 3, 2019 12:15 PM
  • Is THREADPOOL wait high for this particular query? In that case, it could just be a victim rather than the cause.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 3, 2019 12:33 PM
  • Hi nonno,

    What is your version of SQL Server?

    OPENXML is circa SQL Server 2000 era.

    XML has its merits:

    • SQL Server supports native XML data type
    • Hierarchical structures
    • Validation of stored procedures parameters against XML Schema (XSD)
    • XQuery language
    • etc.

    Please share sample of input XML and SQL that inserts it into a table.

    Here is an alternative method how to handle XML parameters in stored procedures:

    DECLARE @xml XML = '<Tickets>
       <Ticket>
          <BetType>0</BetType>
          <BetAmount>100</BetAmount>
    	  <PendingAmount>700</PendingAmount>
    	  <Remark>Some remark</Remark>
       </Ticket>
       <Ticket>
          <BetType>1</BetType>
          <BetAmount>200</BetAmount>
    	  <PendingAmount>780</PendingAmount>
    	  <Remark>Another remark</Remark>
       </Ticket>
    </Tickets>';
    
    -- INSERT INTO #TmpTickets -- uncomment when you are ready
    SELECT col.value('(BetType/text())[1]','TINYINT') AS BetType
        , col.value('(BetAmount/text())[1]','BIGINT') AS BetAmount
        , col.value('(PendingAmount/text())[1]','BIGINT') AS PendingAmount
        , col.value('(Remark/text())[1]','VARCHAR(1024)') AS Remark
    FROM @xml.nodes('/Tickets/Ticket') AS tab(col);


    • Edited by Yitzhak Khabinsky Monday, June 3, 2019 11:38 PM
    • Marked as answer by nonno Tuesday, June 4, 2019 9:53 AM
    Monday, June 3, 2019 12:48 PM
  • I agree with Dan that OPENXML is unlikely to be the cause for the threadpool waits. I would be more inclined to look at bad application behaviour like not closing connections properly.

    However, there is a potential problem with programmers using OPENXML - they may ignore to call sp_xml_remove_document. Again, this does not lead to threadpool waits, but it can lead to memory leaks. (Almost the only way you as a programmer can cause a memory leak in SQL Server.) Memory leaks should testify in different ways than threadpool waits, but...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 3, 2019 10:07 PM
  • Thanks! I will change it to XQuery first, see whether it helps.
    Tuesday, June 4, 2019 4:31 AM
  • Or can it be solved by increasing the max worker threads server option? It's 2008R2 enterprise 64 bits, 32x logical CPUs. According to BOL, it only has 960 worker threads.
    Tuesday, June 4, 2019 7:57 AM
  • Or can it be solved by increasing the max worker threads server option? It's 2008R2 enterprise 64 bits, 32x logical CPUs. According to BOL, it only has 960 worker threads.

    Maybe. If the problem is a misbehaving application that does not close threads, it will not.

    If the application really needs those threads, maybe. But I believe you can get other nasty problems.

    What is the setting for max degree of parallelism? If it's 0, set it to 4 or 8. That will help if the issue that too many processes want to run parallel queries.

    Replacing OPENXML with XQuery is long overdue as suggested by Yitzhak. Nevertheless, if there are a lot of places where OPENXML is used, it will take time and require thorough testing. And it may not solve the problem at all.

    In essence, the correct approach to solve a problem like this is not to change settings blindly, but first make an analysis of why it happens in the first place.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by nonno Tuesday, June 4, 2019 9:47 AM
    Tuesday, June 4, 2019 9:16 AM
  • Thank you Erland. Actually we already set the MAXDOP server option to 1. And rather than increasing the max worker threads, I will try to reduce sql blocking first.
    Tuesday, June 4, 2019 9:51 AM