What sort of pain or comfort should I feel from the dentist during a procedure?

Pin prick. You should have an injection to numb the area, and most dentists use a topical anesthetic before inserting the needle, which insertion should be no more painful than the smallest pin prick, and then you will be thoroughly numb and feel nothing during the procedure. If the procedure does not cut or traumatize your gums then you should have no real pain afterward either.
Not much. If your dentist gets you numb, the only thing you should feel is a pin prick and pressure. Afterwards, there may be some soreness in the gums. If a crown or filling is done, you may experience some sensitivity to cold for a few days but that will go away. If a root canal or extraction, you can expect to be sore for a few days to a couple of weeks.