none
In-Memory OLTP Non-Durable Tables

    Question

  • Hi All,

    I created In-Memory OLTP Non-Durable Tables in 4 different  SQL servers.Lately one of the server performance has degraded and  I see lot of processes (Almost 200-300) running with command XTP THREAD_POOL.I don't see any such processes running on other 3 servers and there isn't any performance issues on them either.

    Does anybody have any idea how to resolve it.

    

    Tuesday, May 15, 2018 8:00 PM

All replies

  • What version you are  using ? Do you see some related records in the ERROR.LOG?

    https://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 16, 2018 4:25 AM
    Moderator
  • I am using SQL server 2016 verson.And I dont see any errors in error logs.These are not going away even after rebooting.

    

    Wednesday, May 16, 2018 1:45 PM
  • What does it return?

    ;WITH Waits AS
    (
      SELECT 
        wait_type,
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',   N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP',
    N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR'
    )
    )
          --filter out additional irrelevant waits
    ,cte1
    AS
    (
    SELECT 
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct
      ,rn
     --- CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1

     --- JOIN Waits AS W2
      ---  ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    -----HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ) SELECT * FROM 
    ( SELECT wait_type,wait_time_s,pct,SUM(pct) OVER(ORDER BY rn
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty FROM cte1
    ) AS Der WHERE runqty <95
    ORDER BY runqty;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 16, 2018 1:50 PM
    Moderator
  • Here is the result.

    wait_type   wait_time_s    pct      runqty
    HADR_CLUSAPI_CALL 150231.36 35.81 35.81
    QDS_ASYNC_QUEUE 149859.2 35.72 71.53
    PWAIT_DIRECTLOGCONSUMER_GETNEXT 81107.56 19.33 90.86


    Wednesday, May 16, 2018 2:02 PM
  • Nothing wrong, do you observe some performance degradation?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 17, 2018 5:09 AM
    Moderator
  • Yes.I ran a batch with physical tables and it completed in 4hrs but when I changed tables into in-memory non durable tables it took 4hrs 30 mins and I see lot of such processes.But this is not the case with other 3 servers.
    Thursday, May 17, 2018 2:32 PM
  • How you define the table? What indexes does it have?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 21, 2018 5:40 AM
    Moderator