none
DMV How to interpret sys.dm_os_ring_buffers and MEMORYCLERK_SQLUTILITIES?

    Question

  • HI ALL,

    While troubleshooting Connectivity problems:

    i found in sys.dm_os_ring_buffers

    SslProcessingInMilliseconds>1640</SslProcessingInMilliseconds>

    What is the meaning of SSLprocessingInMS?

    Please Can anyone explain me line by line output of sys.dm_os_ring_buffers?

    Another issue: BOL is not clear on DMVs.

    =============

    select * from   sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLUTILITIES'

    To resolve DBCC memory issue i found Paul randal suggestion, to look for 'MEMORYCLERK_SQLUTILITIES'. What the use of 'MEMORYCLERK_SQLUTILITIES' in DBCC Commands instaed of backups?


    Manish
    Thursday, October 27, 2011 3:24 PM

Answers

  • Hi Manish,
    The dmv sys.dm_os_ring_buffers DMV is undocumented which may be changed or unavailable in a future release. For more information, please have a look at this article: sys.dm_os_ring_buffers

    >> To resolve DBCC memory issue i found Paul randal suggestion, to look for 'MEMORYCLERK_SQLUTILITIES'. What the use of 'MEMORYCLERK_SQLUTILITIES' in DBCC Commands instaed of backups?
    Did you refer to the reply by Paul Randal on this thread? The type of 'MEMORYCLERK_SQLUTILITIES' memory clerk in sys.dm_os_memory_clerks DMV here stands for the memory consumed by DBCC command.  There are other operations such as backup/restore, Log Shipping and mirroring can reflect on it, see this thread: Memoryclerk_SQLUtilities process leaking memory?

    Best Regards,
    Stephanie Lv

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by LearnerSql Monday, October 31, 2011 11:31 AM
    Monday, October 31, 2011 7:00 AM
  • See http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx for details on the connectivity ring buffers.

    Here are some details on the sections:

     

        <LoginTimers>
     
          <TotalLoginTimeInMilliseconds>21837</TotalLoginTimeInMilliseconds>  <- Total time spent processing login (includes all of below)
     
          <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>  <- Time spent attempting to enqueue login (waiting for a thread to process login)
     
          <NetworkWritesInMilliseconds>2</NetworkWritesInMilliseconds>   <- Time spent performing network writes during login
     
          <NetworkReadsInMilliseconds>77</NetworkReadsInMilliseconds>    <- Time spent on network reads during login
     
          <SslProcessingInMilliseconds>3</SslProcessingInMilliseconds>   <- Time spent processing SSL during login
     
          <SspiProcessingInMilliseconds>21756</SspiProcessingInMilliseconds>   <- Time spent doing SSPI (integrated login)
     
          <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>  <- Login trigger and resource governor time during login
     
        </LoginTimers>

     

    Note every login to SQL Server performs a small amount of SSL to encrypt the login packet.  This is done to ensure sql user id and password and other details in the login packet are protected over the wire.

    Monday, October 31, 2011 6:46 AM

All replies

  • HI ALL,

    While troubleshooting Connectivity problems:

    i found in sys.dm_os_ring_buffers

    SslProcessingInMilliseconds>1640</SslProcessingInMilliseconds>

    What is the meaning of SSLprocessingInMS?

    Please Can anyone explain me line by line output of sys.dm_os_ring_buffers?

    Another issue: BOL is not clear on DMVs.

    =============

    select * from   sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLUTILITIES'

    To resolve DBCC memory issue i found Paul randal suggestion, to look for 'MEMORYCLERK_SQLUTILITIES'. What the use of 'MEMORYCLERK_SQLUTILITIES' in DBCC Commands instaed of backups?

    • Edited by LearnerSql Thursday, October 27, 2011 3:17 PM
    • Merged by KJian_ Monday, October 31, 2011 6:22 AM duplicate
    Wednesday, October 26, 2011 7:19 PM
  • See http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx for details on the connectivity ring buffers.

    Here are some details on the sections:

     

        <LoginTimers>
     
          <TotalLoginTimeInMilliseconds>21837</TotalLoginTimeInMilliseconds>  <- Total time spent processing login (includes all of below)
     
          <LoginTaskEnqueuedInMilliseconds>0</LoginTaskEnqueuedInMilliseconds>  <- Time spent attempting to enqueue login (waiting for a thread to process login)
     
          <NetworkWritesInMilliseconds>2</NetworkWritesInMilliseconds>   <- Time spent performing network writes during login
     
          <NetworkReadsInMilliseconds>77</NetworkReadsInMilliseconds>    <- Time spent on network reads during login
     
          <SslProcessingInMilliseconds>3</SslProcessingInMilliseconds>   <- Time spent processing SSL during login
     
          <SspiProcessingInMilliseconds>21756</SspiProcessingInMilliseconds>   <- Time spent doing SSPI (integrated login)
     
          <LoginTriggerAndResourceGovernorProcessingInMilliseconds>0</LoginTriggerAndResourceGovernorProcessingInMilliseconds>  <- Login trigger and resource governor time during login
     
        </LoginTimers>

     

    Note every login to SQL Server performs a small amount of SSL to encrypt the login packet.  This is done to ensure sql user id and password and other details in the login packet are protected over the wire.

    Monday, October 31, 2011 6:46 AM
  • Hi Manish,
    The dmv sys.dm_os_ring_buffers DMV is undocumented which may be changed or unavailable in a future release. For more information, please have a look at this article: sys.dm_os_ring_buffers

    >> To resolve DBCC memory issue i found Paul randal suggestion, to look for 'MEMORYCLERK_SQLUTILITIES'. What the use of 'MEMORYCLERK_SQLUTILITIES' in DBCC Commands instaed of backups?
    Did you refer to the reply by Paul Randal on this thread? The type of 'MEMORYCLERK_SQLUTILITIES' memory clerk in sys.dm_os_memory_clerks DMV here stands for the memory consumed by DBCC command.  There are other operations such as backup/restore, Log Shipping and mirroring can reflect on it, see this thread: Memoryclerk_SQLUtilities process leaking memory?

    Best Regards,
    Stephanie Lv

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by LearnerSql Monday, October 31, 2011 11:31 AM
    Monday, October 31, 2011 7:00 AM
  • Our server is spending most of the LoginTimer times doing LoginTriggerAndResourceGovernorProcessingInMilliseconds. What could be the reason for this?
    Wednesday, April 04, 2012 12:54 PM
  • This means you either have a login trigger defined for the server or you have setup a resource governor classifier that is checking incoming connections.  You can verify if there is a login trigger by running select * from sys.server_triggers and check for resource governor classifier by running select * from sys.resource_governor_configuration.


    Matt

    Wednesday, April 04, 2012 3:32 PM
  • We did not set triggers or custom resource governor config. select * from sys.server_triggers returned 0 rows and select * from sys.resource_governor_configuration returned one with classifier_function_id=0 and is_enabled=0.

    We are running db server with SQL 2008 R2 on Win 2008 R2 and sporadically (happens every few days for 10 - 50 mins) our application is not able to open new connections to db server. Application times out on get_connection.

    Here is an excerpt from sql server error log - first OleDbException: Timeout expired in our application log occurred at 12:04:27

    2012-04-03 11:50:56.14 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 11:51:44.34 Logon       Login succeeded for user 'DOM\syslog'. Connection made using Windows authentication. [CLIENT: 10.99.100.35]
    2012-04-03 11:51:48.47 spid57      SQL Trace ID 3 was started by login "DOM\syslog".
    2012-04-03 11:52:39.06 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 11:55:56.26 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 11:56:33.23 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 11:58:20.02 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:00:00.45 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 12:00:01.03 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 12:00:09.62 Backup      Database backed up. Database: master, creation date(time): 2012/03/15(14:27:14), pages dumped: 386, first LSN: 208:163:72, last LSN: 208:193:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\10.99.100.75\master\master_backup_2012_04_03_120008_6891803.bak'}). This is an informational message only. No user action is required.
    2012-04-03 12:00:10.41 Backup      Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 187, first LSN: 26:183:37, last LSN: 26:199:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\10.99.100.75\model\model_backup_2012_04_03_120008_7983803.bak'}). This is an informational message only. No user action is required.
    2012-04-03 12:00:12.84 Backup      Database backed up. Database: msdb, creation date(time): 2010/04/02(17:35:08), pages dumped: 2572, first LSN: 292:136:124, last LSN: 292:188:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\10.99.100.75\msdb\msdb_backup_2012_04_03_120008_7983803.bak'}). This is an informational message only. No user action is required.
    2012-04-03 12:00:53.51 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:00:56.39 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 12:01:00.76 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:03:06.83 Backup      Database backed up. Database: uPayDB_Prod, creation date(time): 2012/03/07(07:32:23), pages dumped: 160411, first LSN: 30570:1641:140, last LSN: 30570:1895:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\10.99.100.75\App01_Prod\App01_Prod_backup_2012_04_03_120008_7983803.bak'}). This is an informational message only. No user action is required.
    2012-04-03 12:03:15.99 Logon       Login succeeded for user 'DOM\sqlserv'. Connection made using Windows authentication. [CLIENT: <local machine>]
    2012-04-03 12:04:01.84 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:04:11.73 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:05:06.43 Logon       Login succeeded for user 'appuser1'. Connection made using SQL Server authentication. [CLIENT: 10.99.100.75]
    2012-04-03 12:05:31.09 spid56      Error: 18056, Severity: 20, State: 29.
    2012-04-03 12:05:31.09 spid56      The client was unable to reuse a session with SPID 56, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
    2012-04-03 12:05:31.09 spid55      Error: 18056, Severity: 20, State: 29.
    2012-04-03 12:05:31.09 spid55      The client was unable to reuse a session with SPID 55, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    There are no error in sql error log immediately before failure ID 29. We then run a select against ring_buffers (used this query http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9a41ced9-19ad-4c4f-83ac-7e877b699a8f/) to get more info, and the results were some SniConsumerError 18456 (State 38) and 17830 (States 102, 103), with the latter having LoginTriggerAndResourceGovernorProcessingInMilliseconds account for almost all the time. I could not find a description of what these 102 and 103 states mean.

    Wednesday, April 04, 2012 5:26 PM