I know it's not really specific to SQL Server but in my experience developers are often forget that someone has to use their end product so they make too much effort to provide a technical solution, throwing in some random indexes for fun (because they clearly didn't consider what would be useful to the actual users), a bit of denormalisation because they think it will help inspite of the bodge they had to do to make this module over here work and don't worry the users will never use that feature anyway.

Being a bit more serious the biggest developer issue I hit is providing an application to end users where the users are allowed to make changes to key information so breaking relationships between tables. Why oh why, I spend my life at the moment having to fix the end result and it really bugs me.