3

Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement.

Will the manually created statistics get auto updated when AUTO_UPDATE_STATISTICS is ON. Is there any advantage one over another. I went through the Google and MSDN didn't get much about the difference.

Why am asking here is we are maintaining a script that we used to create statistics manually after each create table script. But in few scripts we missed to keep the statistics script so few statistics got generated automatically. Will this have an impact

References

  1. STATISTICS
  2. UPDATE STATISTICS
4

The only major difference is that SQL Server will only ever create single column statistics. You can create multi-column and filtered statistics. If any of the statistics you created were those types, they may be important. If not, SQL Server is likely to have created system statistics on those single columns, when deemed useful to a query.

Unfortunately you have to do a bit of digging to figure out which statistics your queries are using.

See here for checking the plan cache.

See here for checking queries as you execute them. Note in the comments that on 2014+ you require a different trace flag.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.