locked
SQL Server 2012 - RESOURCE MONITOR / PREEMPTIVE_XE_CALLBACKEXECUTE high CPU usage RRS feed

  • Question

  • Hello, 

      We are currently in the process of migrating an existing clustered SQL Server 2008 R2 instance over to a clustered SQL Server 2012 instance as we phase out the Windows Server 2008 with SQL Server 2008 R2.

      The setup is identical for the SQL Server 2012 instance as it is on the SQL Server 2008 R2 instance.  (meaning the RAM and CPU are both the same or better on the SQL Server 2012 instance)

      The process in which we are migrating is that we're moving a few databases over to the new SQL Server 2012 instance each night.  What we've noticed is that the CPU usage is much higher on the SQL Server 2012 instance than on the previous SQL Server 2008 R2 instance even though the there is only 1/2 of the databases migrated to the 2012 instance. 

      Running the following script:

    ;with cte ([totalCPU]) as (select sum(cpu) from   master.dbo.sysprocesses)
    select 
    	  tblSysprocess.spid
    	, tblSysprocess.cpu
    	, CONVERT(BIGINT,(tblSysprocess.cpu * CONVERT(BIGINT,100))) / CONVERT(BIGINT, cte.totalCPU) as [percentileCPU]
    	, tblSysprocess.physical_io
    	, tblSysprocess.memusage
    	, tblSysprocess.cmd
    	, tblSysProcess.lastwaittype
    from   master.dbo.sysprocesses tblSysprocess
             cross apply cte
    order by tblSysprocess.cpu desc
    
    Produces the following results:

    In a clustered environment, is this normal and if not, does anyone know what this means or how to reduce the CPU usage?

    Thanks.

    Monday, August 18, 2014 4:40 PM

All replies

  • Hello,


    Is SQL Server Audit enabled on that instance? Could you try to disable it?

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, August 18, 2014 5:20 PM
  • Hi,

    Resource monitor taking memory was a bug in SQL Server 2008 and was fixed with subsequent CU. recourse monitor should not consume high memory.

    Can you post the output for

    select @@Version

    Os it 32 bit or 64 bit server. Is it physical or Virtual server ?

    How much is load on new 2012 as compared to 2008 r2.

    Did you updated stats and rebuilt indexes after migrating databases ?

    Please have a look at below link

    http://blogs.msdn.com/b/psssql/archive/2009/08/20/high-cpu-consumed-by-resource-monitor-due-to-low-virtual-memory.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    Monday, August 18, 2014 5:33 PM
  • PREEMPTIVE_XE_CALLBACKEXECUTE is being used when starting and stopping Extended Event sessions - which is the same technology that Auditing uses, as Alberto also suggested

    in my article "Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load" I give some reference on that

    using XEvents (filter on Wait Type map_key = 586) you can narrow it down to the sessions/queries which contribute to this wait


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 5:39 PM
  • Thanks Alberto, I'm using SQL Server 2012 (here are the results from @@Version)

    Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
     Dec 28 2012 20:23:12
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    Here is an example, I have 100 of the smallest databases on SQL Server 2012 right now that I've migrated from the SQL Server 2008 R2 instance.  The SQL Server 2008 R2 instance still has our heavy users on it.  The CPU usage on the Windows Server 2008 server where the SQL Server 2008 R2 instance is running on is averaging 8%.  The Windows Server 2012 with SQL Server 2012 SP1 running on it is averaging 62% CPU usage.

    All the databases I'm migrating to the new SQL Server 2012 came from the SQL Server 2008 R2 server, so I think this has something to do more with the setup or configuration of the SQL Server not the databases.

    Also - I have Max Memory set 35 GB on the SQL Server 2008 R2 and 45 GB on the SQL Server 2012 right now to try to eliminate memory pressure on the OS.

    Thanks

    Monday, August 18, 2014 5:40 PM
  • before analyzing too much: could you install Service Pack 2 for SQL Server 2012?

    http://support.microsoft.com/kb/2958429


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 5:54 PM
  • Thanks Andreas, I don't have too much experience with XEvents - do you mean run a query like this?

    SELECT *
    FROM sys.dm_xe_map_values
    WHERE (map_key = 586)
    

    I read your article, but just not sure what I'm actually supposed to run.

    Thanks again for all your help.

    Monday, August 18, 2014 6:10 PM
  • The server is in production so unfortunately it's not going to be easy to do. So if I can't figure out anything then I'll have to have this scheduled for this weekend during a scheduled downtime.
    Monday, August 18, 2014 6:11 PM
  • Hello,


    The following query may help us identify extended events or audit configurations running on that SQL Server server that are producing that high CPU usage scenario.

    SELECT

    s.name AS 'Sessions'

    FROM sys.server_event_sessions AS s

    WHERE s.name <> 'system_health' and s.name <> 'AlwaysOn_health';



    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, August 18, 2014 6:17 PM
  • Thanks Andreas, I don't have too much experience with XEvents - do you mean run a query like this?

    SELECT *
    FROM sys.dm_xe_map_values
    WHERE (map_key = 586)

    I read your article, but just not sure what I'm actually supposed to run.

    ...

    Hi

    no, that query returns nothing, that's how I found the map_value itself ;-)

    586 is the key that you need in the filter.

    If you haven't looked at Extended Events I would advise you doing to. Profiler is kinda dead ;-) (I am sorry for colloquial English, but I couldn't resist :) )

    Creating XEvent Sessions is described here: http://msdn.microsoft.com/en-us/library/hh213147.aspx

    In Code that session could look like this

    CREATE EVENT SESSION [SQL_Wait_PREEMPTIVE_XE_CALLBACKEXECUTE] ON SERVER 
    ADD EVENT sqlos.wait_info(
        ACTION(sqlserver.sql_text)
        WHERE ([wait_type]=(586))),
    ADD EVENT sqlos.wait_info_external(
        ACTION(sqlserver.sql_text)
        WHERE ([wait_type]=(586))) 
    ADD TARGET package0.event_file(SET filename=N'SQL_Wait_PREEMPTIVE_XE_CALLBACKEXECUTE',max_file_size=(100))
    WITH (TRACK_CAUSALITY=ON)
    GO
    
    

    I expect the wait_info_external to be the only event necessary, but I haven't tested it with this wait_type sothat way you should get the respective queries either way.

    Feel free to add more actions when diving into it.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 6:21 PM
  • I still feel what you are seeing is not a normal behavior. You should apply SP2 and then come back if you have same issue.

    <font color="blue">Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it. <br/> <br/> <b><a href="http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx">My TechNet Wiki Articles </a></b></font>

    Monday, August 18, 2014 7:27 PM
  • Thanks Andreas, so funny thing I ran those scripts, and first of all, Extended Events is awesome. Better than Profiler. Anyway, I was running those and I was finding that I would get a lot of Extended_Info's (probably 100's per second). It wouldn't have any SQL_Text though.  What happened was something happened and the SQL Server service crashed, and restarted momentarily. I just looked through all the logs and everything is fine, but the Memory and CPU usage cleared / dropped to next to nothing (as expected). Re-running that XEvent all those events were gone.

    So I think what I need to do is probably install SP2 and see if that helps.

    Thanks.

     
    Monday, August 18, 2014 7:53 PM
  • I was getting 100's of these per second prior to restarting the service, now they are totally gone.

    There is unfortunately no SQL_Text field though..

    Any ideas?

    Thanks again.

    Monday, August 18, 2014 8:10 PM
  • Woho..We should lower the burden of the XEvents trace. You would not analyse that many events anyways

    try collecting only the first 300 or so

    CREATE EVENT SESSION [SQL_Wait_PREEMPTIVE_XE_CALLBACKEXECUTE] ON SERVER 
    ADD EVENT sqlos.wait_info(
        ACTION(sqlserver.sql_text)
        WHERE (([wait_type]=(586)) AND ([package0].[counter]=(300)))),
    ADD EVENT sqlos.wait_info_external(
        ACTION(sqlserver.sql_text)
        WHERE (([wait_type]=(586)) AND ([package0].[counter]=(300)))) 
    ADD TARGET package0.event_file(SET filename=N'SQL_Wait_PREEMPTIVE_XE_CALLBACKEXECUTE',max_file_size=(100))
    WITH (TRACK_CAUSALITY=ON)
    GO
    
    

    Your events should not be lost. They have been written to the specified file - I avoid the ring_buffer usually for that very reason/case.

    If the sql_text is not in there, there are ways to get it using causality tracking (which I set to on for this very reason).. but that is another story then

    In any case I stick to first installing SP2, Yes :)


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 8:20 PM
  • I was getting 100's of these per second prior to restarting the service, now they are totally gone.

    ...

    There is unfortunately no SQL_Text field though..

    Any ideas?

    Thanks again.

    I only read this after I posted my reply.

    That is different from what I thought (sql_text being empty) - it looks like the column isn't there at all if the screenshot is right.

    Now I am confused. I checked my XEvent trace on my machine: even for wait_info_external I get this action/column...

    XEvent_wait_info_external


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 8:28 PM
  • Thanks a million for all your help Andreas, I think I'm getting closer. I will plan to install SP2 during down time. If I added TRACK_CAUSALITY and ran it, I now get the attach_activity_id.guid and the attach_activity_id.seq, but still no SQL_Text. Is there a way with the GUID to get the SQL Text?

    Thanks again.

    Monday, August 18, 2014 8:35 PM
  • I do get it on some of them, but the ones that are coming in 100's per second that field doesn't appear (SQL_TEXT).
    Monday, August 18, 2014 8:41 PM
  • Ryan: and you sure did use my script and or added the sql_text action to both events?

    To get to the SQL statemnts otherwise you need to add something like sql_statement_starting and then match the guids.

    In this blog post you find a description on how to do such from A-Z:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/11/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions.aspx


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 8:44 PM
  • I do get it on some of them, but the ones that are coming in 100's per second that field doesn't appear (SQL_TEXT).

    I see

    that somehow makes sense then. It's perfectly possible that there is no sql_statement attached to it directly. Try to find out where it comes from like what session id etc to isolate it step by step (by adding the appropriate filters)

    It's also possible to get a callstack, but I would save that as a last resort if SP2 doesn't help and all other attributes of the event don't form a picture.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 8:50 PM
  • OK Andreas, thanks again for all your help, I've learned a lot today. So I tried the SQL_Statement_Starting and it never appears. The process ID also is the same SQL Server process ID, so that doesn't help either.

    Anyway, I'll install SP2 and see.  Thanks again for all your help.

    Monday, August 18, 2014 9:21 PM
  • You're welcome

    wait until after SP2 and hope you don't need to dive in again.

    and if then let us know. (I'd go for session_id rather than process_id ;-) )

    Hope it helped .. XEvent skills are always usable - glad I could convince another one to forget Profiler :)


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 9:45 PM
  • I'm going to be watching some pluralsight videos tonight on extended events, I really like it better already than Profiler, and I liked Profiler.

    As far as session_Id - is there any way to get back to the what the heck is executing all these?  If I get the session ID then where would I go with that?

    Thanks again.

    Monday, August 18, 2014 10:05 PM
  • I'm going to be watching some pluralsight videos tonight on extended events, I really like it better already than Profiler, and I liked Profiler.

    As far as session_Id - is there any way to get back to the what the heck is executing all these?  If I get the session ID then where would I go with that?

    Thanks again.

    Yes, Jonathan's sessions are definitively recommendable.

    If I had a session_id I'd trace that very session altogether, meaning what it's doing by filtering session_id ;)

    have fun and good luck


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Monday, August 18, 2014 10:11 PM
  • I'm still working on this issue.  We upgraded to SP2 and the problem still persists. I ended up working with Microsoft support, however I'm still trying to convince them there is an issue for some reason. They are saying that SQL Server 2012's expected CPU usage should be that it goes up during high activity and then it should rest at about 20% after that even when there is no usage on the server.  I don't believe that's the case.

    We ended up finding that I'm getting about 1000 of these statements ran every 10 seconds:

    select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid from [DB NAME HERE].[sys].[filetable_updates_863498305] with (readpast) order by table_i

    Filestream features are disabled and SQL Server Always On has never been enabled.  I have 2 clusters each with 2 servers having this issue.  So it's something happening between on more than 1 server.

    Anyone have any ideas?  Thanks.

    Tuesday, September 16, 2014 2:32 AM
  • Hi Ryan,

    is the hardware the same? I mean, even if you are in a virtual environment, what kind of physical CPU are installed on the SQL 2008 and what type on the SQL 2012?

    Tuesday, September 16, 2014 9:10 AM
  • Thanks Andrea, the hardware is not the same between the clusters but the resources allocated to it are. The CPU on the 2012 environment is faster.  Both are running on VMware, and I have not over allocated the RAM on either.   Memory Ballooning is off as well.

    I am starting to think this is some sort of bug or memory leak in SQL Server 2012 that I'm running into. 

    Tuesday, September 16, 2014 12:46 PM
  • Hi Ryan,

    please let me know:

    Physical CPU on the VMware hosts

    Is there CPU reservation on one of the VM?

    Have the ESX host CPU resource available?

    Is the ESX the same version?

    Please try to run a benchmark test like hammerDB on both vm and share the results

    Tuesday, September 16, 2014 12:50 PM
  • If anyone else is having this issue, I've been working with Microsoft for nearly a month to try to figure this out.  We don't have it figured out yet, but basically what we found with our servers is that it's fine until it hits max memory or close to it.  (Max memory that SQL server is allowed to use set on the instance in Properties, Memory, then Maximum Server Memory)  When it hits max memory there should be the memory cleanup operations that occur, and when it starts it just can't clean up the memory.  The memory cleanup operations just keep getting ran over and over which causes the high CPU to sit there even when users aren't on the system.  Eventually performance degrades and then eventually the SQL Server service runs out of available memory and the service restarts / crashes.  Microsoft thinks they narrowed it down to 1 thing which is that it's a UserStore_SchemaMgr clerk which continues to grow and never shrinks.

    If anyone else is having this issue here are a few things to try first:

    1) Make sure your databases are corruption free (do a DBCC CHECKDB) on all databases and make sure there is no corruption.  If there is then repair it and reboot the server.

    2) Install this hotfix (I'm doing this tonight, so I don't know if this fixes it yet, I have to do this off hours)  http://support2.microsoft.com/kb/2904100

    3) If no corruption exists from #1, run this: DBCC MEMORYSTATUS.  (Change the query type to have the results output as text from the top toolbar)  Then do Edit, Find and search for UserStore_SchemaMgr. 

    It's measured in KB, so you have to divide that number by 1024 for MB and 1024/1024 for GB.  In our case we have it going into the GB's.  I have a Windows 2008 R2 SQL Server that this runs at about 8 MB.  On our 2012 server it's running at 15 GB.

    Hopefully we'll figure this out, and I'll try to post the fix or workaround.

    Friday, October 10, 2014 2:41 PM
  • Hi Ryan,

    How did go?

    I had similar problem on sql server 2014, the cause is min server memory was set to 0, so every minute the sql servers try to clean up the memory, and all sql server instances seems to be doing the job at the same time(we probably cannot see cpu spike if there is only one sql server instance), I have now increased min server memory, it looks good now.

    Cheers,

    Albert

    Friday, November 14, 2014 4:40 AM
  • Thanks Albert, we have Max Memory enabled - unfortunately we are still troubleshooting with Microsoft.  Nobody knows quite yet what our deal is.

    Thanks.

    Friday, November 14, 2014 11:47 PM
  • So after months of working on this, we believe we found the bug in SQL Server that is causing our issue. What we found was that if you are creating temporary tables, and then either altering or adding a column to them, then the memory can't be reclaimed during memory cleanup operations.  (Memory Leak).

    What happens next is that as memory usage overall grows SQL Server begins it's cleanup operations which results in high CPU.  After the cleanup operation is done, it realizes memory is still high so it starts again.  This takes about 20% CPU.  So that's where my constant 20% CPU utilization is going.

    By restarting the service it clears that memory and CPU goes back to low.

    If you don't restart the service and memory climbs to the max memory allotted to SQL Server, eventually the service will start rejecting some queries and the service will basically eventually stop working.

    This bug will hopefully be fixed in the SQL Server 2012 SP2 CU5.

    Wednesday, January 28, 2015 3:51 PM
  • Ryan

    I had similar issues with SQL Server 2012 SP2.  Hyper-V VM running Server 2012 R2 (latest updates).  VM configured with 4096 MB Startup Memory and a 512-8192 dynamic running memory.  VM would always run with 3.7 / 4 GB committed.  Max memory was reported as 8 GB (Task Manager)

    SQL Server process would always consume 3-10 % CPU.  Looking at sp_who2, I saw Resource Monitor and Lazy Writer consuming lots of CPU cycles.  Running the queries mentioned above, I was seeing the same issues you were.

    Activity Monitor would not run.  sp_who2 would take over 1 minute to run sometimes.  Some queries took a long time to run.

    I tried maxing the memory of SQL Server, but no help.  I then turned to the VM settings.  I set the memory allocation; turning off dynamic memory and setting the VM memory to 8192 MB.

    Started the VM and no more SQL CPU issues.

    What triggered my changed of the VM memory allocation was that even though the VM reported 3.7/4.0 GB committed memory, Hyper Server reported 1162 MB memory assigned the to VM guest.  So maybe there is a problem with SQL Server, Windows and committed memory in relation to how the Hypervisor assigns it.  I don't claim this to be a solution, it is just an empirical observation that might be helpful in your quest for a solution.

    Mike

    Friday, February 6, 2015 4:16 AM
  • Thanks Mike, that's a strange issue.  I think there was maybe something similar I saw when working on this, where HyperV would essentially allow for the over allocation of memory on the host itself even though the VM would only report a certain number that was less.  Sounds kind of like what you were experiencing. 

    Friday, February 6, 2015 3:50 PM
  • I'm still working on this issue.  We upgraded to SP2 and the problem still persists. I ended up working with Microsoft support, however I'm still trying to convince them there is an issue for some reason. They are saying that SQL Server 2012's expected CPU usage should be that it goes up during high activity and then it should rest at about 20% after that even when there is no usage on the server.  I don't believe that's the case.

    We ended up finding that I'm getting about 1000 of these statements ran every 10 seconds:

    select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid from [DB NAME HERE].[sys].[filetable_updates_863498305] with (readpast) order by table_i

    Filestream features are disabled and SQL Server Always On has never been enabled.  I have 2 clusters each with 2 servers having this issue.  So it's something happening between on more than 1 server.

    Anyone have any ideas?  Thanks.

    Since yesterday I suddenly (After an automatic windows update? Don't know.) have the same issue more or less completeley blocking the SqlExpress (2012) instance on my Windows 10 dev box.

    After all these posts are over a year old: any solutions yet?

    THX a lot!


    • Edited by art-ist Wednesday, November 4, 2015 1:00 PM
    Wednesday, November 4, 2015 1:00 PM
  • Hello, I did resolve the high CPU issue which was caused by a memory leak when creating a temporary table and then adding columns to it.  When the columns were added to a temporary table, the table would never get properly disposed of so the memory would keep growing.  When the memory started getting high, SQL Server would start running it's cleanup brokers which would cause the high CPU.  Eventually it gets to a point where it just runs non stop which gets you the 25% CPU.  This issue is resolved in SQL Server 2012 SP2 CU5.  As far as I know though the issue may still exist in SQL Server 2014, but not sure.

    As far as the FileTable statements above, those apparently are OK - they are used for FileStreams even if they aren't used.  There is a traceflag I got from Microsoft that disabled those, but they are harmless.  If you want the traceflag, let me know I can find it.

    Does that help?

    Wednesday, November 4, 2015 3:26 PM
  • As far as the FileTable statements above, those apparently are OK - they are used for FileStreams even if they aren't used.  There is a traceflag I got from Microsoft that disabled those, but they are harmless.  If you want the traceflag, let me know I can find it.

    Hi there -- I know it's been 6 months but can you provide the trace flag information? Thanks in advance!

    >L<


    http://spacefold.com/lisa

    Tuesday, May 31, 2016 9:58 PM
  • Sure, it's either -T272 or -T5566. I think it's 5566 but it was an undocumented trace flag if I remember correctly.

    Let me know if that helps.

    Tuesday, May 31, 2016 10:08 PM
  • I'm seeing something very similar in SQL Server 2014 SP2. After a restart CPU utilization is low, but after a few days CPU utilization ramps to 20%. I just increased the Max Memory, and the CPU dropped again.

    This shows that RESOURCE MONITOR is using most of the CPU.

    Does anyone know if this is an issue on SQL Server 2014 SP2?

    Thursday, October 27, 2016 7:11 PM
  • Hello,

    When we found this bug Microsoft did test it against SQL Server 2014 as well and the code was the same so the bug did exist in both.

    According to the below article:

    https://support.microsoft.com/en-us/kb/3032476

    This has been resolved in 2014 SP1.

    The thing that you probably want to find is what clerk is actually growing.  To do this I think you need to do this:

    Run this: DBCC MEMORYSTATUS.  (Change the query type to have the results output as text from the top toolbar) 

    Then do Edit, Find and search for UserStore_SchemaMgr. 

    It's measured in KB, so you have to divide that number by 1024 for MB and 1024/1024 for GB. 

    Thursday, October 27, 2016 7:27 PM
  • This is what I see

    USERSTORE_SCHEMAMGR (node 0)             KB
    ---------------------------------------- -----------
    VM Reserved                              0
    VM Committed                             0
    Locked Pages Allocated                   0
    SM Reserved                              0
    SM Committed                             0
    Pages Allocated                          112984

    Thursday, October 27, 2016 8:18 PM
  • Hmm, so that only looks like 110 MB, for me when this bug existed, it was into the GB's range.  What you may have to do is try to search around in the results of the DBCC MEMORYSTATUS query and see if any of the pages allocated are really high.  I'm no expert (just play one on TV) but this probably sounds like something different. 

    The USERSTORE_SCHEMAMGR clerk is for temporary tables, so this bug would never release the temp tables which would cause the memory cleanup operations to just execute continuously.

    Unfortunately must of this stuff isn't documented real well at least to the public so if you find the clerk that's consuming an abnormal amount of memory you may want to open up a ticket with Microsoft support.

    • Edited by Ryan_Ha Thursday, October 27, 2016 8:54 PM
    Thursday, October 27, 2016 8:52 PM