Sql-server – SQL Server statistics

Data is bulk inserted into these tables in the order of ~ 200k rows per day... out of ~5 million rows

That's 200k new rows that do not have any stats for, so querying for those values will make the optimizer come up with scans because of the outdated stats do not inform it that the new values are in the range sampled. Force a stats update after the bulk insert.

Not sure autostats can help given that 200k inserts fall bellow the 5 million 20% cadinality trigger, see Statistical maintenance functionality (autostats) in SQL Server.

Would it be a potential bottle neck on performance if a statistic had already been created automatically for the same column(s) by SQL Sever and my script creates a duplicate statistic?

Surely you can write your script such that it detects such a duplicate statistic, instead of just blindly creating another one (possibly multiple times)? We do this for tables, views, procedures, functions, indexes... why not for statistics too? If you have the ability to create a statistic you have the ability to check the metadata for one that's already there (and maybe just needs to be updated).

I haven't done any performance tests, and I'm not sure if auto-stats (sync or async) will kick off multiple times or just pick one of them, but certainly nothing good can come of it. In the one case you're going to spend twice (or more!) the amount of time updating redundant statistics; if they don't both get updated then it's possible that the optimizer will pick the wrong one when using the statistic for cardinality.

I'd be more interested to know how often you find that - with auto-create stats turned on - SQL Server hasn't created a statistic that would yield better performance or estimates, and then suddenly the plans improve because of the statistic you decided to create (that SQL Server didn't bother). My guess is that would be pretty rare (unless you haven't really enabled auto-create stats for those customers, or maybe these are filtered statistics, though that isn't possible for your 2005 folks).

As for stats going stale in general, you may be able to implement Trace Flag 2371 - which improves the thresholds for engaging auto-stats - for some of your customers.