On almost all Arduino boards, you need to modify something before debugging is possible. We will start with the three most popular boards, Uno, Nano, and Pro Mini, and then go through the list of less well-known devices listed in the Tools->Board->Arduino AVR Boards menu.
Modifiying an original Arduino Uno board
On the original Arduino Uno boards, there is a solder bridge that can be cut to remove the connection to the auto-RESET capacitor (see the next picture).
Modifying an Arduino Uno clone with CH340 USB-UART converter
On clones with a CH340 serial converter chip, it is capacitor C8 that you need to remove.
Modifying a Seeduino 4.2
The Seeeduino 4.2 is basically an UNO, but has some additional nice features, such as a switch for changing supply voltage. You can program and debug it the same way as an UNO is programmed and debugged. You need to remove the auto-RESET capacitor C14 (blue ellipsis) and the RESET-LED (violet) as shown below.
Original Arduino Nano
Things are a bit more complicated with Arduino Nano boards. Here, you not only have to remove the auto-RESET capacitor but also a strong pull-up resistor of 1kΩ on the RESET line. This is impossible for the original boards because the resistor is part of a resistor array. You may try to cut the trace from Vcc to the resistor, but I doubt this can be done without damaging other parts of the board.
Modifying an Arduino Nano clone with CH340 USB-UART converter
For Arduino Nano clones (those using a CH340 as the serial converter), one can remove the resistor (cyan) and the capacitor (blue), as described by denMike. But you should definitely do that before soldering the pins to the board.
Arduino Pro Mini (original or clone)
The Arduino Pro Mini is a simple case. The pull-up resistor has 10 kΩ, and the auto-RESET capacitor is not connected as long as nothing is connected to the DTR pin. This is the header pin, either labeled DTR or GRN. On the original board by Sparkfun (left), this is the bottom pin; on some clones (right), it is the top one.
The only drawback is that the board does not have space for an ISP connector. This means you must use six jumper cables to make the necessary connections.
Arduino Uno Mini
There is no provision on the Uno Mini board for cutting off the auto-reset capacitor. According to the schematic, one needs to remove capacitor C3.
Modifying an Arduino Duemilanove
Similar to the Arduino Uno, the Duemilanove has a solder bridge labeled RESET EN that needs to be cut. It is at a different place, though (picture from Wikipedia).
Modifying an Arduino Diecimilia
I never had such a board in my own hands. However, there is a nice description by Steven Cogswill on how to remove the crucial 100 nF on such a board. And here is the photo from his website.
Arduino Mini
I have never seen any such board. However, if you happen to see one of those, there are 3 versions of it: Mini 03, Mini 04, and Mini 05. While the former two types of board have only an Atmega8 on board (which does not have debugWIRE), the latter has an ATmega328P on board. The RESET line has just a 10 kΩ pull-up and you need an FTDI board to connect to the board. So nothing has to be modified here.
Arduino Ethernet
This board has an FTDI header similar to the Pro Mini. So, make sure not to connect the DTR pin and you are safe.
Arduino Fio
Another board, I never had on my desk. And another board that can be programmed with an FTDI adapter. Again for this reason, no hardware modifications are necessary, as long as no XBee is plugged in or a FTDI-adapter is connected.
Modifying an Arduino BT
From the schematics, it looks as if you need to remove capacitor C1. This will not hurt any auto-RESET capabilities because there were none from the beginning.
LilyPad Arduino Simple
This board carries an ATmega328P, but not all ISP pins are made available, which means that debugWIRE debugging is impossible.
Arduino Pro
Similar to the Pro Mini by Sparkfun, no modifications are necessary. The only thing to observe is to leave the DTR pin unconnected.
Arduino NG
This board does not have an auto-RESET capacitor onboard. So, no modifications are necessary. However, you find descriptions of how such a capacitor could be added.
Arduino Gemma
This board carries an ATtiny85 MCU. The RESET line is not connected to anything and can be accessed through a pad on the bottom of the board.
Modifying Arduino Uno Wifi (Rev. 1)
This board is basically an Uno board with an ESP32 for WiFi. It contains the RESET-EN bridge, which can be cut to enable debugWIRE debugging.
Other boards
There are many more boards in the Arduino AVR Boards list. However, those other boards all carry non-debugWIRE MCUs such as ATmega32U4, ATmega1280, or ATmega2560.