If you run the following command on a Linux/Unix machine, among other things, you get a prompt for a password:
$ ssh-keygen -t dsa
Enter passphrase (empty for no passphrase):
What does adding a password to your SSH key actually do? Is it more secure to use a password when prompted?
Adding a password to your SSH key means that the private key will be stored encrypted on your local machine. That private key is a rather sensitive secret since knowing that key grants access to all servers on which you registered the corresponding public key (in your .ssh/authorized_keys file).
Secrets in local files can be plundered by attackers in various occasions:
This list is not exhaustive. In all these occasions, password-based encryption of your SSH private key will make the life harder for the attacker. Note that your files are likely to contain other sensitive information as well, so SSH private key encryption is not sufficient to reach the beatitude of absolute security; however, it helps.
Of course, there is nothing completely free. If you use a password to protect the on-disk storage of your private key, then you will have to type that password on a regular basis. As usual, security and convenience are two sides of a trade-off.
Many users do not encrypt their hard drive. If you leave an ssh private key on an unencrypted hard drive, anyone with physical access to the hard drive can come along and steal your private key.1 Then they can use this key (along with computers you frequently connect to with that key taken from ~/.ssh/config, ~/.ssh/known_hosts, ~/.history) to connect to a bunch of other machines you have access to, which is generally a very bad thing.
Granted, if you have physical access to the machine with the private key stored on it for long enough, it is also possible with little more effort to install a hardware keylogger and capture the passphrase to the private key/encrypted drive, assuming the user cannot notice that you tampered with it.
1 All an attacker has to do is either take out the hard drive from the machine and either mount it externally in another machine or boot into another operating system (e.g., from a live CD/USB/OS), or just boot into single user mode. Then they can copy the private ssh key even if the read permissions were limited in the original OS and the login for the user with read permissions had a very strong password.
It prevents root or other users who might have access to your key file (by design, by accident, or by privlege escalation) from using your key to log into other systems.
As to -t dsa in particular, that is up for debate. I believe this answer regarding DSA remains accurate as it relates to OpenSSH at this time.
The rest of this answer will use the phrase 'encrypted key' to mean a key encrypted with modern best practices that should be as infeasible to crack as it can be (and that exists nowhere else on or around the same storage medium unencrypted).
As other answers have stated, the passphrase is an additional level of protection for your key. Keys can give you the convenience of being able to authenticate without using a passphrase, but if utilized that way your key wherever you store it is at greater risk of being used by people not you.
A passphrase does make it harder to abuse a key, ...but if you are security minded (as you probably are if you're concerned with SSH), it does not make it a lot harder.
If you have an unencrypted drive and an unencrypted key, anyone with physical access only needs to read your filesystem and they can authenticate as you would. Given the usual lack of security most buildings have, and that the average person at a workplace probably has at least required their OS to ask them for a password to log in (not to decrypt their data which is encrypted), this means to access that data one would have to only do something pretty simple like walk up to and boot the system from a live OS, remove the storage & read it with something else, or simply steal the storage medium or the entirety of whatever houses it. I would wager most average computer users (but not necessarily most users of SSH) have computers in this state of insecurity.
If you have an encrypted key in this scenario, and the person who physically walked up to your computer and took your data didn't already know it was encrypted and left, they would have to come back again and do something further to also steal your passphrase. But...
If you have an encrypted key in this scenario, and the person who physically walked up to your computer and took your data came for your key, it stands to reason they might expect your key would be encrypted and having gained physical access will likely need to do little extra to acquire that passphrase. This is generally referred to as an evil maid attack and can take countless forms, but the bottom line is that anyone who can witness or access (personally or via a surreptitious device) the physical elements you use to authenticate via (network cable, network router/switch, keyboard, mere unobstructed view of keyboard, etc.) can likely acquire your passphrase. So the physical security of a location you authenticate from or to is also quite important.
If your drive is also encrypted, the same attack can likely be used to learn how to authenticate for both the drive and the key during the same session. This means whether only the drive/filesystem is encrypted or only the key is encrypted makes little difference to the difficulty of accessing the key (to people who aren't leaving their keys in random places, anyway).
Now mostly for fun, let's say you thoroughly encrypted a key and nobody ever authenticates with it again, and someone who stole it therefore has no guaranteed way to steal the passphrase for it. Even in this scenario a key can be less secure than using a passphrase without a key, because a passphrase stored only in a person's own memory will always tend to be harder to access than a passphrase that can be eternally brute force tested for in a key that exists in digital storage outside of a person's own memory.
