I just came back from a trip to Sicily. Night time photography is a whole new world with high ISO. I have the Nikon D750 and it can go up to 52,000. I took pictures in dark churches and it popped the colors. Same with evening and night pictures of mountains and caves. I need to review and edit before I post any. I was amazed at the difference between the Nikon D7000 and the Nikon D750. In other posts the Nikon D500 has an even broader range. I was also surprised at the quality of my wife's iPhone 6 pictures. It does very well is reasonable light.

Paul, your question is pretty general. High ISO is there, but I avoid it whenever possible. If there is action and low light it's a necessary evil.
In such cases, especially if the light is changing I use Auto-ISO. It will give you the LOWEST ISO for the combination you feel you need to use.
For me, pretty much 6400 is max before the noise is just way to unsightly.
Anytime you can use flash, do so.
There is NO substitute for a fast lens for low light, but then you have DoF issues. I try to not remove noise at the expense of sharpness.
High ISO is sometimes just a necessary evil!!!
SS

Today, with digital, far higher ISO are usable than were ever possible with film.

I shot a lot of ISO 50 and 100 slide film.... 200 tops. If I needed 400 in color, I switched to color neg film. With B&W I shot a lot of fine grain ISO 100 and below, but also more than a little ISO 400 (Tri-X, etc.) and sometimes pushed that to 800 or 1600.

With digital, it's another story....

Even as early as 2004, a 6MP Canon 10D was great at ISO 800 in color;



5 or 6 years ago, the image below was shot with a 21MP Canon 5DII at ISO 6400, with no special processing at all... just a RAW conversion using Lightroom at default settings.



Two months ago I shot an event with a pair of 20MP 7D Mark II, new to me, at ISO 8000 and 16000, after doing some test shots, including the image below at 16000...



Yes, there is some noise in the above image, which is a RAW conversion done in Lightroom at default settings. I've made some 11x14 prints from images shot at ISO 16000 at the event... with some extra post-processing.

One thing with really high ISOs is to avoid underexposure. You don't want to have to boost exposure in post-processing, as that will also greatly increase the appearance of image noise. Nikon/Sony sensors are a little bit more tolerant of underexposure at ISOs up to 800, but at 1600 and higher Canon is about the same.

Image "noise" occurs due to heat and crosstalk between individual pixel sites. It's essentially the camera recording an incorrect color at a particular pixel site. In color it can look pretty ugly.

There is noise reduction built into the cameras and in post-processing software. In addition to the NR built into Lightroom and Photoshop, with really high ISO shots I use a more advanced Noiseware plug-in. I've recently been experimenting with Nik NR plug-in, too. There are several others.

In-camera NR is two types.... high ISO and long exposure.... that are handled a bit differently. High ISO NR simply tries to deal with and correct odd colors at a fine pixel level in images. Long exposure NR (1 second or longer) in a lot of cameras is actually done by taking two shots.... the second one being a "blank" shot with the shutter closed, which the camera uses to identify where noise is occurring and then delete it from the first image. (I don't know about other brands of cameras, but in Canon if you have LENR enabled and forget how it works, think something is wrong and cancel the second exposure, the first one is automatically deleted too!)

Another thing you can do to deal with image noise is convert to black & white. In monotone, the noise looks more like film grain, which is a lot less objectionable.

Keeping the cat theme going, even in 2007 an 8MP Canon 30D ISO 800 image is easily handled, the "grain" doesn't even show up at Internet sizes and resolutions...



I've run out of cat-theme high ISO images, but you might even want to use higher ISOs deliberately, for a more film-like look....



Above was shot with 5DII at ISO 800, which it handles easily.

I use ISO 400 as my default with 7D-series cameras. (Original 7D actually got noisier are lower ISOs... maybe they're programmed to turn off in-camera NR below 400.)

With today's sensors you can go much higher on the iso but it depends on the sensor, software, and what the images will be used for. I have a Nikon 7100 and use iso 800 for night sports and because I am posting the images for computer screen viewing 150 dpi they turn out fine. My suggestion is to bracket an immage at a full range of ISO values to see what your camera can do in some different shooting situations. I think you might be supprised at what modern sensors and the camera software can do theese days. Of course you need to be using some late model equipment.