xp_cmdshell slow response

• Gil_Adi

  SSCommitted

  Points: 1669

  More actions

  April 17, 2013 at 11:31 pm

  #276514

  I am using xp_cmdshell to bcp out a small table to a file and than copy it inside the local disk.

  1. The system (memory , cpu , disk queue) are all not loaded with work.

  2. The table is very small and the bcp results in 1 KB file.

  3. The bcp operation varies under the xp_cmdshell between 700ms to 2200 ms .

  4 Using cmd prompt from the windows os the same operation of bcp takes 31 ms. (login are the same as sql service)

  5. The xp_cmdshell operates fast on another server.

  6. The same operation performed 2 weeks ago worked fast on this server.

  Somthing had changed but what to look for ?:hehe:

• Gil_Adi

  SSCommitted

  Points: 1669

  More actions

  April 18, 2013 at 5:30 am

  #1607648

  Here Are sample Code

  Stores Is a small Table Less Then 1000 Rows and less then 300 bytes per row

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

  --Drop the session if it exists.

  IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorWaits')

  DROP EVENT SESSION MonitorWaits ON SERVER

  GO

  CREATE EVENT SESSION MonitorWaits ON SERVER

  ADD EVENT sqlos.wait_info

  (WHERE sqlserver.session_id = 499 /* session_id of connection to monitor */)

  ADD TARGET package0.asynchronous_file_target

  (SET FILENAME = N'C:\EE_WaitStats.xel',

  METADATAFILE = N'C:\EE_WaitStats.xem')

  WITH (max_dispatch_latency = 1 seconds);

  GO

  --SELECT xmv.map_key, xmv.map_value

  --FROM sys.dm_xe_map_values xmv

  --JOIN sys.dm_xe_packages xp

  -- ON xmv.object_package_guid = xp.guid

  --WHERE xmv.name = 'wait_types'

  -- AND xp.name = 'sqlos'

  --order by map_value desc

  --GO

  --Start the event session

  ALTER EVENT SESSION MonitorWaits ON SERVER STATE = START;

  GO

  --- from Heare To the and of xp_cmdshell commends it takes 9 seconds

  --********************************************************

  declare @sSql VARCHAR (1000)

  select @sSql = ''

  Select @sSql = ' bcp MyDataBase..Stores '

  Select @sSql = @sSql + ' out ' + 'C:\' + 'GIL.dat' + ' -U'+ 'MyUser' +' -P'+ 'MyPassword' +' -S ' + @@SERVERNAME + ' -c -t -r -CRAW '

  exec master..xp_cmdshell @sSql,no_output

  select @sSql = ''

  Select @sSql = ' bcp MyDataBase..Stores '

  Select @sSql = @sSql + ' out ' + 'C:\' + 'GIL2.dat' + ' -U'+ 'MyUser' +' -P'+ 'MyPassword' +' -S ' + @@SERVERNAME + ' -c -t -r -CRAW '

  exec master..xp_cmdshell @sSql,no_output

  Select @sSql=''

  Select @sSql= ' Copy C:\Gil.Dat C:\Gil3.Dat'

  exec master..xp_cmdshell @sSql,no_output

  Select @sSql=''

  Select @sSql= ' Copy C:\Gil2.Dat C:\Gil4.Dat'

  exec master..xp_cmdshell @sSql,no_output

  Select @sSql=''

  Select @sSql= ' Del C:\Gil?.Dat '

  exec master..xp_cmdshell @sSql,no_output

  --********************************************************

  -- Stop the event session

  ALTER EVENT SESSION MonitorWaits ON SERVER STATE = STOP;

  GO

  IF OBJECT_ID ('tempdb..#RawEventData') IS NOT NULL DROP TABLE #RawEventData

  GO

  --Create intermediate temp table for raw event data

  CREATE TABLE #RawEventData (

  Rowid INT IDENTITY PRIMARY KEY,

  event_data XML);

  GO

  --Read the file data into intermediate temp table

  INSERT INTO #RawEventData (event_data)

  SELECT

  CAST (event_data AS XML) AS event_data

  FROM sys.fn_xe_file_target_read_file (

  'C:\EE_WaitStats*.xel',

  'C:\EE_WaitStats*.xem', null, null);

  GO

  SELECT

  waits.[Wait Type],

  COUNT (*) AS [Wait Count],

  SUM (waits.[Duration]) AS [Total Wait Time (ms)],

  SUM (waits.[Duration]) - SUM (waits.[Signal Duration]) AS [Total Resource Wait Time (ms)],

  SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]

  FROM

  (

  SELECT

  event_data.value ('(/event/@timestamp)[1]', 'DATETIME') [Time],

  event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') [Wait Type],

  event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') [Op],

  event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') [Duration],

  event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') [Signal Duration]

  FROM #RawEventData

  ) waits

  WHERE waits.[Op] = 'End'

  GROUP BY waits.[Wait Type]

  ORDER BY [Total Wait Time (ms)] DESC;

  GO

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

  This are the results

  Wait Type Wait Count Total Wait Time (ms)Total Resource Wait Time (ms)Total Signal Wait Time (ms)

  WRITELOG 1 1 1 0

  Is there another event class I should monitor on xp_cmdshell