My question is regarding the creation of statistics as suggested by SQL Server 2005 Database Tuning Advisor: I thought I heard a speaker recently state that you could safely create any suggested statistics without concern. I know that this is not a good practice when it comes to creating suggested indexes, and that bad things can happen if you're not careful with them. This gives me pause when it comes to statistics.
I know that allowing the server to auto-create statistics is considered to be a best practice. Considering this, it would seem that we've already given SQL free license to create statistics at will. So this would seem to support that idea.
I'd appreciate any input.Thanks!
asked Nov 20, 2012 at 10:15 PM in Default
Check out Holger Schmelling's booklet on the topic.
Statistics are "safer" than, say, indexes. The reason is that creating a statistic doesn't take up very much space. The downside is that, like indexes, you need to maintain statistics. For tables which get updated very heavily or for extremely large tables, this is not an incidental cost. Granted, updating stats is a less-intense operation (and if you're rebuilding indexes, you're already updating stats for free), so the marginal cost is lower.
Auto-creating statistics is typically a good idea (there are exceptions, like when you know exactly which stats you need), but the biggest area where you as a DBA could "beat the system" is multi-column statistics. If you know that certain combinations of columns get used together, that's a piece of domain knowledge that the database engine doesn't have available. The Database Tuning Advisor can also figure out that you might need multi-column statistics, so you could have seen some of those.
answered Nov 20, 2012 at 10:56 PM