I had one 18mths or so ago following post menopausal bleeding. A scan revealed a thickened womb lining so a biopsy was suggested along with the fitting of a Mirena Coil.
I took two paracetamol and two ibuprofen an hour or so before the procedure and then had a local anaesthetic gel followed by a local anaesthetic. I felt occasional pinches, like uncomfortable period pain, but on the whole I'd call it discomfort rather than painful.
I know lots of women have bad experiences but mine was OK and I'd do it again if I had to (but obvs really hope not to!)