Hunting for select * with Extended Events

After a question from Alex Whittles (t|w) during my Extended Events session at the Leicester SQL Server User Group about monitoring for rough SELECT * FROM queries sneaking into production I decided to have a look.

The trick comes in using the special predicates comparators provided in Extended Events. I’ve not really come across much need for these, as I can normally get by with the usual ,= and others. Like everything else in Extended Events, these can be found in the sys.dm_xe_object management view:

select xp.name, xo.name, xo.description, xo.type_name, xo.type_size from
sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp on xo.package_guid=xp.guid
where object_type like 'pred_compare'

which gives these (abbreviated) results:

name name description type_name type_size
package0 greater_than_max_uint64 Greater than the maximum 64-bit unsigned int value previously seen uint64 8
package0 less_than_min_uint64 Less than the minimum 64-bit unsigned int value previously seen uint64 8
package0 equal_int64 Equality operator between two 64-bit signed int values int64 8
package0 not_equal_int64 Inequality operator between two 64-bit signed int values int64 8
package0 less_than_min_float64 Less than the minimum 64-bit double value previously seen float64 8
package0 equal_i_ansi_string Equality operator between two ANSI string values ansi_string 0
package0 not_equal_i_ansi_string Inequality operator between two ANSI string values ansi_string 0
package0 less_than_i_ansi_string Less than operator between two ANSI string values ansi_string 0
package0 less_than_equal_i_ansi_string Less than or Equal operator between two ANSI string values ansi_string 0
package0 greater_than_i_ansi_string Greater than operator between two ANSI string values ansi_string 0
package0 greater_than_equal_i_ansi_string Greater than or Equal operator between two ANSI string values ansi_string 0
package0 equal_i_unicode_string Equality operator between two UNICODE string values unicode_string 0
package0 not_equal_i_unicode_string Inequality operator between two UNICODE string values unicode_string 0
sqlserver like_i_sql_unicode_string LIKE operator between two SQL UNICODE string values unicode_string 0
sqlserver like_i_sql_ansi_string LIKE operator between two SQL ANSI string values ansi_string 0

The interesting ones for the purpose of Alex’s question are the last 2, like_i_sql_ansi_string and like_i_sql_unicode_string. So from that it’s obvious that the Extended Event engine wants us to use a filter appropriate for the data type. A quick look into a extended Events result set for sql_statement_completed gives this fragment for statement:

<data name="statement">
    <type name="unicode_string" package="package0" />
        <value>select * from msdb.dbo.backupmediafamily
        </value>
</data>

So we need the unicode version, so we’ll try:

/*
Note, there are no predicates to restrict which sessions, dbs or planets this is looking at. So be careful where you run it as you could get more data than you expected
*/

create event session SelectStarHunt on server
add event sqlserver.sql_statement_completed
(where (sqlserver.like_i_sql_unicode_string(statement,N'select *%')))
add target package0.ring_buffer;

alter event session SelectStarHunt on server state=start;
go

select * from sys.all_columns where 1=2
select object_id, name from sys.all_columns where 1=2

select cast(target_Data as XML) from sys.dm_xe_session_targets xt
inner join sys.dm_xe_sessions xs on xt.event_session_address=xs.address
where xs.name='SelectStarHunt'

declare @target_Data xml
select @target_data=cast(target_Data as XML) from sys.dm_xe_session_targets xt
inner join sys.dm_xe_sessions xs on xt.event_session_address=xs.address
where xs.name='SelectStarHunt'

select c.value('(data[@name="statement"]/value)[1]','varchar(max)') as result
from @target_Data.nodes('/RingBufferTarget/event') as T(c)

which gives us the single result:

result
select * from sys.all_columns where 1=2

So, yes, we can track all SELECT * FROM‘s that appear.

If you were going to try this in anger, I’d recommend starting with a simple Counter target first to quickly see if you have a large or small number of potential captures, and then work on a filter set from there.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

2 Responses to Hunting for select * with Extended Events

  1. Mohanraj Jayaraman August 16, 2013 at 05:27 #

    like_i_sql_unicode_string does this predicate exists in SQL 2K8 R2 ? if not exists how to make use of like operator in the extended events.
    My requirement is to capture the application name otherthan .Net Sql Client using Extended Events.

    Thanks,
    Mohanraj

    • Stuart Moore
      Stuart Moore August 19, 2013 at 07:43 #

      Hi Mohanraj,
      Unfortunately the like predicate comparisons were only introduced with SQL Sever 2012.

      Are you just wanting to capture this information for audit purposes? Or do you need to capture it live for problem analysis?

      If it’s the former then a logon trigger might be solution for you.

      For the latter I can think of a couple of possible solutions, but a lot would depend on exactly what you were tracking as that would affect exactly where in the session I’d want to be filtering the data to get the least impact on performance.

      Regards
      Stuart

Leave a Reply