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:
|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_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)','varchar(max)') as result from @target_Data.nodes('/RingBufferTarget/event') as T(c)
which gives us the single 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.