none
FT_IFTS_RWLOCK waits during heavy search activity.

    Soru

  • Wer're seeing excessive waits under sys.dm_os_waiting_tasks for the type FT_IFTS_RWLOCK.  this only occurs during heavy activity.  Total waiting tasks will range from 600-800, with an average wait time of 500-600 ms.  Search performance for single keyword contains queries will take several seconds, when normally they're sub-second.   Complex contains queries will go from 1-2 seconds to timing out at 30 seconds.

    Search performance is seems pretty constant, then it "falls off a cliff" and the FT_IFTS_RWLOCK waits appear.

    We're in the process of re-writing the application to perform fewer FT searches, but need a band-aid until then.

    thanks!

    Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

    Machine has 24 cores/72GB ram

    12 Mart 2012 Pazartesi 02:54

Yanıtlar

  • I've narrowed the issue down to spinlock contention somewhere inside the IFTS system (per SQLCAT paper ).  

    Our queries were relatively simple

    SELECT t.a, t.b, t.c ... 
    FROM t WHERE CONTAINS(SearchTerms, @searchCondition) AND ....

    We found that often we'd often get fulltext seeks inside of a nested loop.  These plans performed orders of magnitude worse than any other plans.

    We re-wrote the queries to use a temp table for the fulltext portion of the queries, and huge improvement!

    SELECT [Key]  INTO #tmpMatches FROM CONTAINSTABLE(t, SearchTerms, @searchCondition)

    SELECT t.a, t.b, t.c ... FROM t WHERE id IN (select [Key] from #tmpMatches)  AND ....


    • Yanıt Olarak İşaretleyen J. Granden 20 Mart 2012 Salı 21:03
    20 Mart 2012 Salı 21:03

Tüm Yanıtlar

  • Hi J.Granden,

    What is the maximum server memory size for the SQL Server instance? In case of memory issue which leads to the full text search performance degradation, you may pay closely attention to the memory usage by SQL Server. Please use the query below to return the detail information:

    select 
            type,
            sum(virtual_memory_reserved_kb) as [VM Reserved],
            sum(virtual_memory_committed_kb) as [VM Committed],
            sum(awe_allocated_kb) as [AWE Allocated],
            sum(shared_memory_reserved_kb) as [SM Reserved], 
            sum(shared_memory_committed_kb) as [SM Committed],
            sum(multi_pages_kb) as [MultiPage Allocator],
            sum(single_pages_kb) as [SinlgePage Allocator]
    from 
            sys.dm_os_memory_clerks 
    group by type
    order by type


    According to the output, if the virtual_memory_committed_kb is approached to the virtual_memory_reserved_kb, there might be memory pressure on SQL Server. You may consider increasing the volume for the maximum server memory, and also try to grant Lock Pages in Memory privilege to the SQL Service account to reduce page faults.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    13 Mart 2012 Salı 10:42
  • I'll post the results of the query when I get into the office.

    Max Server Memory is set to 64 GB.  Lock Pages in memory is already set. windows shows around 1.5 GB memory free (Memory: Available MBytes is 1500-1600) , and no os paging (Memory:Pages Input/sec stays at 0).  Box is dedicated to a single instance of sql server.

    I can rule out external memory pressure (ring buffers always show physical memory high).

    Internal memory pressure definately exists.  Proc cache hit ratio is around 85% due to a poorly written app that generates non-parameterized sql.  Forced parameterization and optimize for ad-hoc workloads are both turned on.

    We had an issue last week where a bad query caused a 12 GB memory grant, which produced the problem at a lower volume than usual, so it definately seems sensitive to internal mem pressure.

    15 Mart 2012 Perşembe 12:43
  • type	VM Reserved	VM Committed	AWE Allocated	SM Reserved	SM Committed	MultiPage Allocator	SinlgePage Allocator
    CACHESTORE_BROKERDSH	0	0	0	0	0	0	8
    CACHESTORE_BROKERKEK	0	0	0	0	0	0	8
    CACHESTORE_BROKERREADONLY	0	0	0	0	0	0	120
    CACHESTORE_BROKERRSB	0	0	0	0	0	0	16
    CACHESTORE_BROKERTBLACS	0	0	0	0	0	0	592
    CACHESTORE_BROKERTO	0	0	0	0	0	0	8
    CACHESTORE_BROKERUSERCERTLOOKUP	0	0	0	0	0	0	8
    CACHESTORE_CONVPRI	0	0	0	0	0	0	64
    CACHESTORE_EVENTS	0	0	0	0	0	0	16
    CACHESTORE_FULLTEXTSTOPLIST	0	0	0	0	0	240	80
    CACHESTORE_NOTIF	0	0	0	0	0	0	16
    CACHESTORE_OBJCP	0	0	0	0	0	1808	27504
    CACHESTORE_PHDR	0	0	0	0	0	32	37128
    CACHESTORE_SQLCP	0	0	0	0	0	432816	7086288
    CACHESTORE_STACKFRAMES	0	0	0	0	0	16	0
    CACHESTORE_SYSTEMROWSET	0	0	0	0	0	0	5560
    CACHESTORE_TEMPTABLES	0	0	0	0	0	0	40
    CACHESTORE_VIEWDEFINITIONS	0	0	0	0	0	0	24
    CACHESTORE_XMLDBATTRIBUTE	0	0	0	0	0	0	8
    CACHESTORE_XMLDBELEMENT	0	0	0	0	0	0	8
    CACHESTORE_XMLDBTYPE	0	0	0	0	0	0	8
    CACHESTORE_XPROC	0	0	0	0	0	0	192
    MEMORYCLERK_BHF	0	0	0	0	0	0	112
    MEMORYCLERK_FULLTEXT	0	0	0	0	0	144	2616
    MEMORYCLERK_FULLTEXT_SHMEM	0	0	0	16960	16960	0	0
    MEMORYCLERK_HOST	0	0	0	0	0	0	32
    MEMORYCLERK_QSRANGEPREFETCH	0	0	0	0	0	0	0
    MEMORYCLERK_SNI	0	0	0	0	0	32	760
    MEMORYCLERK_SOSNODE	0	0	0	0	0	18408	14720
    MEMORYCLERK_SQLBUFFERPOOL	75644928	131072	68658296	0	0	2008	0
    MEMORYCLERK_SQLCLR	0	0	0	0	0	0	8
    MEMORYCLERK_SQLCLRASSEMBLY	0	0	0	0	0	0	0
    MEMORYCLERK_SQLCONNECTIONPOOL	0	0	0	0	0	0	3344
    MEMORYCLERK_SQLGENERAL	0	0	0	0	0	3544	11328
    MEMORYCLERK_SQLHTTP	0	0	0	0	0	0	8
    MEMORYCLERK_SQLOPTIMIZER	0	0	0	0	0	1472	8136
    MEMORYCLERK_SQLQERESERVATIONS	0	0	0	0	0	0	0
    MEMORYCLERK_SQLQUERYCOMPILE	0	0	0	0	0	0	0
    MEMORYCLERK_SQLQUERYEXEC	0	0	0	0	0	0	5832
    MEMORYCLERK_SQLQUERYPLAN	0	0	0	0	0	0	0
    MEMORYCLERK_SQLSERVICEBROKER	0	0	0	0	0	544	168
    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT	0	0	0	0	0	0	48
    MEMORYCLERK_SQLSOAP	0	0	0	0	0	0	0
    MEMORYCLERK_SQLSOAPSESSIONSTORE	0	0	0	0	0	0	0
    MEMORYCLERK_SQLSTORENG	11392	11392	0	0	0	4968	12400
    MEMORYCLERK_SQLUTILITIES	120	120	0	0	0	0	160
    MEMORYCLERK_SQLXML	0	0	0	0	0	0	0
    MEMORYCLERK_SQLXP	0	0	0	0	0	0	16
    MEMORYCLERK_TRACE_EVTNOTIF	0	0	0	0	0	0	0
    MEMORYCLERK_XE	0	0	0	0	0	2232	120
    MEMORYCLERK_XE_BUFFER	12672	12672	0	0	0	0	0
    OBJECTSTORE_LBSS	0	0	0	0	0	5488	384
    OBJECTSTORE_LOCK_MANAGER	264192	264192	0	0	0	0	93128
    OBJECTSTORE_SECAUDIT_EVENT_BUFFER	0	0	0	0	0	0	16
    OBJECTSTORE_SERVICE_BROKER	0	0	0	0	0	0	464
    OBJECTSTORE_SNI_PACKET	0	0	0	0	0	112	11968
    USERSTORE_DBMETADATA	0	0	0	0	0	0	11488
    USERSTORE_OBJPERM	0	0	0	0	0	0	3576
    USERSTORE_SCHEMAMGR	0	0	0	0	0	1400	38088
    USERSTORE_SXC	0	0	0	0	0	0	1736
    USERSTORE_TOKENPERM	0	0	0	0	0	11456	16568

    thanks!

    15 Mart 2012 Perşembe 14:05
  • J.Granden,

    Based on your description, it seems there is not any memory pressure on both SQL Server and Operating System. You may have a look at the execution plan of the target query to analysis the time consumed. Meanwhile, please pay attention to suggestions on this article: Improving SQL Server full-text search performance.


    Stephanie Lv

    TechNet Community Support

    16 Mart 2012 Cuma 08:16
  • I've narrowed the issue down to spinlock contention somewhere inside the IFTS system (per SQLCAT paper ).  

    Our queries were relatively simple

    SELECT t.a, t.b, t.c ... 
    FROM t WHERE CONTAINS(SearchTerms, @searchCondition) AND ....

    We found that often we'd often get fulltext seeks inside of a nested loop.  These plans performed orders of magnitude worse than any other plans.

    We re-wrote the queries to use a temp table for the fulltext portion of the queries, and huge improvement!

    SELECT [Key]  INTO #tmpMatches FROM CONTAINSTABLE(t, SearchTerms, @searchCondition)

    SELECT t.a, t.b, t.c ... FROM t WHERE id IN (select [Key] from #tmpMatches)  AND ....


    • Yanıt Olarak İşaretleyen J. Granden 20 Mart 2012 Salı 21:03
    20 Mart 2012 Salı 21:03
  • did you try this?

    DBCC TRACEON (7646, -1)


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    21 Mart 2012 Çarşamba 00:23