SQL Server – BCP – Memory Constraints

Background

As a follow-up to a recent post titled “SQLServer – BCP Optimization via Running Multiple Instances in Parallel ”  ( https://danieladeniji.wordpress.com/2015/07/22/sqlserver-bcp-optimization-via-running-multiple-instances-in-parallel/ ), let us see what happens on the DB Server has one pushes it a bit.

Monitoring

Activity Manager

Waits

ResourceWaits-Memory

Explanation:

  • We can see that we are really waiting on Memory

Processes

ProcessWaits

Explanation:

  • A more granular view that shows the same thing; as it indicates that our individual BCP requests are suspended as they wait for their memory requests to be fulfilled

WhoIsActive

Running Adam Machanic’s shows..

WhoIsActive

Explanation:

  1. No Blocking Sessions
  2. Relative to writes, a lot of reads on the active BCP sessions
  3. Other BCP Sessions are waiting on RESOURCE_SEMAPHORE

Dynamic Management Views

sys.dm_exec_query_memory_grants

Let us do a little bit more work.

Not that much more as it is Friday, and so we will just align memory requests to the correspondent session’s query.

Query:

SELECT
          [sqlText] = dmvESQT.[text]
        , dmvDMEQMG.session_id
        , dmvDMEQMG.request_time
        , dmvDMEQMG.grant_time
        , [waitInMinutes] =
             case
                when dmvDMEQMG.grant_time is null then
                   datediff(minute, dmvDMEQMG.request_time, getdate())
                then null
             end
        , dmvDMEQMG.requested_memory_kb
        , requested_memory_mb = (dmvDMEQMG.requested_memory_kb / 1024)
        , dmvDMEQMG.granted_memory_kb
        , dmvDMEQMG.required_memory_kb
        , dmvDMEQMG.used_memory_kb
        , dmvDMEQMG.max_used_memory_kb
        , dmvDMEQMG.query_cost
        , dmvDMEQMG.resource_semaphore_id
        , dmvS.login_name

FROM sys.dm_exec_query_memory_grants dmvDMEQMG

    LEFT OUTER JOIN sys.dm_exec_sessions dmvS

       ON dmvDMEQMG.session_id = dmvS.session_id

    outer apply sys.dm_exec_sql_text(dmvDMEQMG.sql_handle) dmvESQT

Output:

requestedMemory_20150729_0931AM

Explanation:

  • Memory Requested
    • We can see that depending on the targeted table and size of the data feed, the requested memory and the query cost varies a bit
    • We can also see that the requested memory, in our case, is about 885 MB; a little below 1 GB
    • Once we take into account this request is for each BCP session, we need a few few GBs of unused memory to simultaneously service our concurrent requests

sys.dm_tran_locks

Let us see what types of locks are being acquired.

Query:


set transaction isolation level read uncommitted;

SELECT

           lck.resource_type
         , lck.request_mode
         , lck.request_status
         , dmvS.login_name
         , lck.request_session_id
         , par.object_id
         --, object_schema_name(par.object_id) as schhemaName
         --, object_name(par.object_id) as objectName
         , [schema] = [schema].name
         , objectName = obj.name
         , COUNT(*) number_locks

FROM sys.dm_tran_locks lck with (nolock)

        INNER JOIN sys.partitions par  with (nolock)

           ON lck.resource_associated_entity_id = par.hobt_id

        INNER JOIN sys.objects obj  with (nolock)

            ON par.object_id = obj.object_id

        INNER JOIN sys.schemas [schema] with (nolock)

            ON obj.schema_id = [schema].schema_id

        INNER JOIN sys.dm_exec_sessions dmvS  with (nolock)

            ON lck.request_session_id = dmvS.session_id

where  [schema].[name] not in ('sys')

GROUP BY
              lck.resource_type
            , lck.request_mode
            , lck.request_status
            , dmvS.login_name
            , lck.request_session_id
            , par.object_id
            , [schema].name
            , obj.name 

Output:

locks_page

Explanation:

  • It looks like though we requested Table Locks, we are getting more granular locks; in this case PAGE LOCKS

Summary

Again, I will suggest that you have yourself a nice LAB to tinker with things.

Your DBA might not grant your request for “VIEW SERVER STATE“.

But, nevertheless find a way around and earn your “Come up“.

Dedicated

Dedicating Sarah McLachlan’s Shipwreck to the beautiful warriors, Jillian Johnson and Mayci Breaux, who “laid down” viewing the movie Train Wreck in New Orleans last night.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s