Alert on AUTO CREATE STATISTICS

Question

Has anyone had success detecting when a _WS_sys% statistic is created (as a result of CREATE STATISTICS = ON)?

I created a DDL trigger using the CREATE STATISTIC event (in code block).  It works fine if someone executes a CREATE STATISTIC; it does not work if a query predicate references a table/column that does not have a statistics on it.

Thanks,

ALTER TRIGGER t_DDL_CreateStatistic
	ON DATABASE
	FOR CREATE_STATISTICS AS
declare	@results	varchar(max);
SET @results = 'New Statistic Created: ' + (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)')) + '.';
SET @results = @results + (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'VARCHAR(255)')) + '.';
SET @results = @results + (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(255)'));
insert into hfc_mart.dbo.alerts (AlertText) values (@results);

Answer 1

You can't get that from a DDL trigger.  The only DDL trigger events are from you running a DDL command (like DROP DATABASE or CREATE STATISTICS).  But the auto create of statistics is not done because you ran a DDL command.

The only place I know to pick that information up is with SQL Trace (or SQL Profiler).  There is a trace event that is named "Auto Stats".  Event columns that might be of interest to you include DataBaseName, ObjectID (which will be the the object id of the table the statistics was created for), TextData which will contain the columns in the new statistics entry, and, of course StartTime and EndTime.

If you run SQL Trace with only that event specified, it will have an extremely low overhead (essentially 0) since it will only trap Auto Stats creations and even then all it does is write one row to the trace.

Tom

Answer 2

Thanks Tom.  I suspected the DDL wasn't going to work for the auto stats... but I had to try.  I was trying to avoid putting any kind of trace, even for a single data point that might not fire very often.  Thanks for the confirmation... I marked your response as the ANSWER.  It may also be possible to do with extended events but for that I will really have to do my homework.