Spring and fall are the busy times. From early March through late May is great weather and everyone know that. Likewise, September and October are also busy. Arrive on a Sunday night for the best selection. As the week goes on, availability becomes more iffy. Avoid the week before Easter, as there's a jeep safari in town and there are lines even to get gas.
We camp in Moab fairly often, as it's just over three hours from our house. We mainly frequent the BLM CG's. Our first choices are those along the river, esp. upstream from US 191. Good access to Moab the parks, and most points of interest from there. Goose Island is closest and has good sized sites (some backing to the river) but fills very quickly. Seems like we end up at Granstaff quite often. Sign at the entrance says 24' limit but I'm guessing many sites will accommodate a 30' or even longer. Big Bend, another 4+ miles up from Granstaff, also has larger sites. Many other CG's in this area are for small rigs (20'), TC's, or tents.
Ken's Lake will accommodate larger rigs, but definitely out in the open when the winds blow.
Along Kane Creek Road (the turn off is near McDonalds in Moab) there's several CG's that are mostly for smaller rigs but a few sites will hold larger ones. But 8 to 10 miles out of town, they have just put in about 100 new campsites at an area called Ledges. If everything else is full, you might find something here. Some protected sites, many in the open. But all new facilities. The road leading out there is graded, but can get dusty and washboard-y making trips back into town a little long.
Horsethief CG is nicely located for visiting Dead Horse Point and Island in the Sky at Canyonlands. It's also a little higher in elevation and thus a little cooler. In a small pinon grove so a little privacy and protection there. A bit of a trip from town.
We've never stayed at Sand Flats east of Moab, but there are`120 sites up there. Looks like good spacing based on their maps and Google. You will be surrounded by mostly mountain bikers and a smattering of motorcycles up there.
There are a couple of NFS CG's up in the LaSals that are much cooler. A longer drive to town, but a totally different experience up there. Generally closed by snow in March and maybe into April. Again in October, snow might become an issue. Not for big rigs, but say 30' should be good, esp. Warner. Oowah might be a little tight. Both are on or near lakes, kind of different for the Moab area.