locked
Removed SQL 2012 AV group leaves phantom processes consuming large amounts of resource RRS feed

  • Question

  • Hi,

    I have been experiencing performance degradation on some production servers that previously were part of a SQL 2012 High availability Group.

    I recently used a small window of opportunity to test the HADRON groups on some of my SQL Servers. Unusually because of the licencing and hardware high spec, identically setup servers required I had to take some of our servers out of the web production pool during a quiet period and use these for testing.

    When the testing concluded, I removed all the HA SQL groups disabled, then removed the windows cluster and removed the cluster Feature from both of the servers and rebooted, but I see the following phantom queries.

    () select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid 
    from [<dbname>].[sys].[filetable_updates_2105058535] with (readpast) 
    order by table_id

    A reboot resolved the issue initially and although unhappy without knowing the root cause I decided not to spend any further time investigating as believed it was resolved. But I noticed a major hit on performance on those specific server again after 2-3 weeks and the queries have returned, if this happens during peak period I will be short of capacity and unable to reboot.

    How can I completely remove trace of this as these are obviously protected system tables?.... there are still the odd days when I wished I was still allowed to meddle in those system tables :)

    Monday, April 15, 2013 10:18 AM

Answers

All replies

  • That system table is related to the filetable feature, not AlwaysOn.

    What is the performance problem you are seeing?

    David



    David http://blogs.msdn.com/b/dbrowne/

    Monday, April 15, 2013 10:48 AM
  • I'd guess that it is enabled in the process somewhere then, because we have never used the filetable feature, only AlwaysON.

    The query detailed above runs for each user database - soaking up CPU i.e. at a fairly quite period this servers CPU is running at almost double another identical spec server in its pool on our website.

    Looking the procedure cache, historically it if you combined the separate plans it spends 90% of CPU on these queries.

    Monday, April 15, 2013 12:16 PM
  • That sounds like a case for support.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, April 15, 2013 12:54 PM
  • you may want to check your alwayson database tables, whether there are filetables?

    Try

    SELECT * FROM sys.filetables;
    GO
    
    SELECT * FROM sys.tables WHERE is_filetable = 1;
    GO


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    Tuesday, April 16, 2013 12:48 PM
  • nope, these do not return any results
    Tuesday, April 16, 2013 4:40 PM
  • can you run against your AV databases? or maybe all your databases just to complete the full picture.

    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    Wednesday, April 17, 2013 10:35 AM
  • that is the point. I no longer have any AV databases, or AV groups, or AV enabled in SQL server, or a windows cluster enabled or even the feature installed.   Running it against any of the db's that did participate in the AV group yields no results on either server.
    Wednesday, April 17, 2013 3:09 PM
  • Hello, I'm having a similar issue but haven't been able to resolve this with Microsoft Support. Did you end up figuring this out?

    Thanks.

    Tuesday, September 16, 2014 2:40 AM
  • Hello, we have the same problem on our mssql-server 2k12. I first noticed it yesterday and was looking for a solution in the web. Even a restart did not solve it in my case, the ominous requests appeared instantly after the reboot. Neither AV nor filetables have ever been configured on the server. Did you find a way to resolve that issue?

    Thanks.

    Friday, March 11, 2016 8:26 AM