Caveat. All answers have an implicit "It Depends"

Q: Why add another unique key when a naturally occurring one already exists?

A: If you have a package system that assigns keys and you wish to migrate from that system then it is useful to have keys that remain in your replacement package system. Product 'x' may have an auto-increment in your original system but a guid in your replacement system (or any other weird and wacky combination). Having a specific ever-lasting key for Product 'x' allows you to reconcile between systems during the interim state and also maintain consistency in your data warehouse system. Remember your data warehouse system will probably outlive your OLTP system by at least a couple of generations. There is nothing that says the immutable key has to be human readable.

Q: What's wrong with an auto-increment.

A: As a simple stand-a-lone system you will get away with it. Syncing multiple systems, each with its own auto-increments can be a real bitch. Been there, done that, learnt new Anglo Saxon. also, failed inserts also increment the auto-increment leaving gaps.

Q: Ever tried to debug using GUIDS.

A: I'm a big fan of the Red-Gate tool kit. I don't work for Red-Gate, I have no financial incentive to promote Red-Gate. SQL Data Compare works for me. I've also spent a lot of time thinking about automated testing and test frameworks to add to my DBA bag of tools. Not having those tools is vastly more expensive than having them. Tools are not a panacea but help greatly.

Q: Never had a problem with GUIDs as primary keys due to their uniqueness?

A: Really??? When assigned in the DB they can cause nightmares when the calling app has a buggy INSERT mechanism. Try inserting the same record twice and without a naturally occurring unique key then the record insert will generate another GUID to go with the duplicate record. Obviously this afflicts auto-increment records as well but at least they are easy to spot.

When I wrote my article about a proposed Key Generator instead of a GUID one of the comments that was posted against the article was that it was somewhat similar to what Twitter do. Guids/uuids are easy for programmers to use but sometimes a key generator is more appropriate.