SQL 2008/R2 Extended Events - Row Count

• sqld-_-ba

  SSCarpal Tunnel

  Points: 4365

  More actions

  March 17, 2018 at 9:18 pm

  #327820

  I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

  In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

    select * from sys.dm_xe_object_columns
    where object_name = 'sql_statement_completed'
   
    select pkg.name as PackageName, obj.name as ActionName
    from sys.dm_xe_packages pkg
    inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
    where obj.object_type = 'action'
    order by 1, 2

  But all they have is reads/writes, but not actual number of rows.

  Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?

• MMartin1

  One Orange Chip

  Points: 27830

  More actions

  March 20, 2018 at 1:32 pm

  #1984027

  select * from sys.dm_exec_sessions

  There is a row count column in there. I think you need active connection at the time of running this, even though a process may have completed already. This could help.

  ----------------------------------------------------

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 20, 2018 at 1:45 pm

  #1984030

  sqld-_-ba - Saturday, March 17, 2018 9:18 PM

  I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

  In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

    select * from sys.dm_xe_object_columns
    where object_name = 'sql_statement_completed'
   
    select pkg.name as PackageName, obj.name as ActionName
    from sys.dm_xe_packages pkg
    inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
    where obj.object_type = 'action'
    order by 1, 2

  But all they have is reads/writes, but not actual number of rows.

  Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?

  I get row_count and last_row_count when I use that on 2014. I think your query is correct. It is not easy dealing with extended events on 2008, I was using the ExtendedEventManager up on Codeplex. It helped quite a bit. I have no idea what you get with the current versions of SSMS. I was using the 2008 version of SSMS and would guess that is what you would need to use. Here is the link to that AddIn if you are interested:
  ExtendedEventManager

  Sue

• sqld-_-ba

  SSCarpal Tunnel

  Points: 4365

  More actions

  March 24, 2018 at 5:36 pm

  #1984495

  .

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  Follow

  March 25, 2018 at 3:34 am

  #1984500

  If you're using SQL 2008 R2, I'd suggest you stick with trace. While XE existed on 2008 R2, it was fairly limited. It became a lot more usable in 2012.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass