Aside from the entertainment benefit players get from the service, does it in any way help the Bitcoin ecosystem to have a large churn of small transactions?
There is value added by this service:
Online wagering can be a little lower house edge, but rarely will you find a house edge of just 1.5% like SatoshiDICE offers and the 1% that BitLotto offers. Here's some background:
To play SatoshiDICE you need bitcoins, so that means there will be additional activity at the exchanges (both in purchases of bitcoins and cashing out by those who lady luck was nice to). The player base won't be just existing bitcoiners as this payout level is such an attraction it will spread by word of mouth -- or virally even.
Most Bitcoin software, websites, documentation and media or press has been for an English-speaking audience. With translations for multiple languages, Bitcoin will reach for the first time many users who would not otherwise learn of it.
Of course, every bitcoin that is held by a SatoshiDICE gambler is a bitcoin that is not sitting in a trader's wallet, waiting for the first hint of a selloff before it gets dumped (i.e., volatility is lower when the coins are not concentrated as much primarily with those speculating.)
After reaching a level where using Bitcoin becomes comfortable, those gambling at SatoshiDICE might find other goods and services that their bitcoins are useful for instead of just cashing out.
Additionally, with this unprecedented success for a Bitcoin startup there will be competition. The technique employed by SatoshiDICE (payouts using same coins as the wager, thus can be paid out using 0/unconfirmed) is novel but easily replicated.
SatoshiDICE is a for-profit venture, and a chunk of those profits will likely be reinvested back into that business (perhaps building a mobile app for it, as an example), and/or other Bitcoin-related endeavors.
The blockchain pruning feature was something Bitcoin's developers hadn't had as top priority but with this level of growth and added data to the blockchain that may become top-of-mind. This may be a feature that will help Bitcoin and SatoshiDICE might be responsible for having it occur sooner than it might otherwise have.
For Bitcoin, SatoshiDICE has win written all over it!
No, SatoshiDICE seems to be generally illegal, which only harms Bitcoin's reputation - and even in jurisdictions where it might be legal, gambling is generally regarded poorly. Additionally, it fills the blockchain with bloat unnecessarily and is easily exploited.