none
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);

    Tuesday, July 02, 2013 5:20 PM

Answers

  • 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

    • Proposed as answer by Naomi NModerator Wednesday, July 03, 2013 3:45 AM
    • Marked as answer by Dave Turpin Wednesday, July 03, 2013 2:16 PM
    Tuesday, July 02, 2013 10:56 PM

All replies

  • 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

    • Proposed as answer by Naomi NModerator Wednesday, July 03, 2013 3:45 AM
    • Marked as answer by Dave Turpin Wednesday, July 03, 2013 2:16 PM
    Tuesday, July 02, 2013 10:56 PM
  • 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.

    Wednesday, July 03, 2013 2:21 PM