July 23, 2009 at 3:02 am
Hi All
I have created a DDL trigger on database for all events, i am recording the event in a table on a separate database , when i checked the table there was a event type called as CREATE_STATISTICS and with object type STATISTICS and Object Name _WA_Sys_00000007_673459DE and command text as --ENCRYPTED--, when checked it seems some statistics have been created on this database by a user,
My Question is what action will trigger this event type, since this is the only entry for that user on the database.
Thanks for all your help in advance.
July 23, 2009 at 5:27 am
That's an automatically created statistics set. The user didn't run anything manually, they ran a query, the query optimiser decided it needed stats that didn't exist and since the db option auto_create_statistics is true, it went ahead and created the stats.
You can ignore that event in a DDL trigger as well as update statistics as both will happen relatively often.
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
July 23, 2009 at 5:42 am
Thanks Gial, Is it a good practice to leave AutoCreateStats on on database, or turn it off
July 23, 2009 at 5:47 am
If you turn it off then the optimiser can't create stats in order to get an estimate of rows affected by a query. If it can't get that estimate, it guesses, often very badly. Bad guesses as to rows affected for a query often lead to very poorly performing queries.
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
July 23, 2009 at 6:02 am
Thanks Giall 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply