• rollercoaster43 (1/27/2015)


    Hi All,

    I have a question out of curiosity.. For Bulk Load requests in SQL server, Are there any specific profiler event? Like the one we have for RPC RPC:Starting and for Batch Requests, we have SQL:BatchStarting.

    Are Bulk Load requests that are being monitored through Profiler captured as SQL:Batch... events at the backend?

    Are there any new features added in 2012 or 2014 to identify a Bulk request submitted through bcp.exe utility or any other sqlbulkcopy program?

    Thanks.....

    Why you are using SQL profiler when you can use Extended Events and uses way less resources?

    And yes, with Extended Events you can capture those. You will see the T-SQL statement.

    I use this to capture those, filtering anything higher than X number of reads:

    --CREATE EVENT SESSION [QueriesWithHighLogicalReads] ON SERVER

    --ADD EVENT sqlserver.sql_batch_completed(

    -- ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)

    -- WHERE ([logical_reads]>200000))

    --ADD TARGET package0.event_file(SET filename=N'E:\DBA_Audit\QueriesWithHighLogicalReads.xel')

    --GO

    Once done, I run this to read and understand the data:

    WITH CTE_ExecutedSQLStatements AS

    (SELECT

    [XML Data],

    [XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME') AS [Time],

    [XML Data].value('(/event/data[@name=''duration'']/value)[1]','int') AS [Duration],

    [XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int') AS [CPU],

    [XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int') AS [logical_reads],

    [XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int') AS [physical_reads],

    [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') AS [SQL Statement]

    FROM

    (SELECT

    OBJECT_NAME AS [Event],

    CONVERT(XML, event_data) AS [XML Data]

    FROM

    sys.fn_xe_file_target_read_file

    ('E:\DBA_Audit\QueriesWithHighLogicalReads*.xel',NULL,NULL,NULL)) as v)

    SELECT

    [SQL Statement] AS [SQL Statement],

    SUM(Duration) AS [Total Duration],

    SUM(CPU) AS [Total CPU],

    SUM(Logical_Reads) AS [Total Logical Reads],

    SUM(Physical_Reads) AS [Total Physical Reads]

    FROM

    CTE_ExecutedSQLStatements

    GROUP BY

    [SQL Statement]

    ORDER BY

    [Total Logical Reads] DESC

    GO

    This can collect lot of data and fill up the disk drive, so please test on a non live box 1st.

    There is plenty of information in Internet about Extended Events. I fully recommend you to Google it and start using it instead of Profiler.