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.
- We can see that we are really waiting on Memory
- 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
Running Adam Machanic’s shows..
- No Blocking Sessions
- Relative to writes, a lot of reads on the active BCP sessions
- Other BCP Sessions are waiting on RESOURCE_SEMAPHORE
Dynamic Management Views
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.
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
- 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
Let us see what types of locks are being acquired.
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
- It looks like though we requested Table Locks, we are getting more granular locks; in this case PAGE LOCKS
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“.
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.