- What is the physical significance (or effect) of the tail being heavy?
The practical concern is that, when the data come from a heavy-tailed process, there will be occasional extreme data values (or outliers) far from the main body of the data.
- Compared to what the tail of the distribution is said to be "heavy"?
The tail of the normal distribution is a good reference point. But see my comments below for caveats.
- Given some data, and their CCDF plots (a log-log plot, say) how do we say its heavy? Simply by curve-fitting with a power-law CCDF?
A simple normal quantile-quantile plot will show the extreme values very clearly.
A note on the Wikipedia definitions of heavy tails: They are in some ways useless in that they require unbounded support for a distribution to be heavy-tailed.
However, it is easy (easier, even) to imagine real processes that produce bounded data than it is to imagine real processes that can produce infinitely large (in magnitude) data. And extreme values exist for bounded distributions as well: Take a U(-1,1) distribution, and mix it with a U(-10000, 10000), with mixing probability .0001. The resulting distribution can be called extremely heavy-tailed, capable of producing extreme outliers, but it has finite support.
Thus, alternative definitions of heavy-tailedness are needed for practical purposes. Measures of kurtosis (moment-based and quantile-based) can be useful alternatives.