x

Statistics suggested by DTA

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!
more ▼

asked Nov 20, 2012 at 10:15 PM in Default

broby425 gravatar image

broby425
50 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 20, 2012 at 10:56 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

+1 for this, particularly the multi-column statistics. It was one of the things that Richard Douglas discussed in a webinar yesterday on performance killers (and, more to the point in this context, how to deal with them).

These have to be created manually, as, while SQL Server will automatically create statistics where it thinks they will help, it will only do this on a single column at a time; multi-column stats still have to be created manually. Have a look at the documentation for CREATE STATISTICS - http://msdn.microsoft.com/en-us/library/ms188038.aspx - for more details.
Nov 23, 2012 at 09:20 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34

asked: Nov 20, 2012 at 10:15 PM

Seen: 633 times

Last Updated: Nov 26, 2012 at 01:55 AM