I realize asking 'how rare/common' is a relative question. In my gedcom, my surname is common but in my local phone directory it is very rare (we are the only family in our rather large town). I recall finding a website that told me the 'rank' of surnames based on how common they are in US but I did not save it and would like to find again.
The census bureau publishes a list of the top 10,000 surnames found in the US census, with statistics on their occurrences. I have used this list in a few projects over the years, and currently in a natural language system that extracts data from obituaries and other genealogical text.
Similar files exist for male and female given names.
As an example here is a link to a page where you can get the statistics on all surnames occurring more than 100 times on the US 2000 census:
How rare or how common a surname is/was is probably relative (to an area, a timeframe, etc.), but census, registrations at the time of births, etc., are often available and provide the necessary data to support that research.
Although your question is asking about the US, some of the information that follows covers the topic more broadly:
- For a listing of top surnames in the 1990 US census, see About.com's 100 Most Common U.S. Surnames The top five surnames in that list are: Smith, Johnson, Williams, Jones and Brown.
- Ancestry.com and some other sites, too, I imagine, sometimes provide a surname overview; at least in the United States. These little info bits used to appear on the Ancestry.com search screen. I'm able to return a more indepth article like this by searching for the surname and Ancestry. Here's a link to their article about the "Jones" name, "Jones Family History." Note there is a distribution map about the surname in the United States that has a slider/timeline.
- Wikipedia has a family of pages dedicated to the concept. "Lists of most common surnames." The pages linked from there are categorized for Asia, East Asia, Central America, Europe, North America, Oceana and South America. I looked over the page for Europe--there are 41 locational subcategories. That page carries a warning that it may contain "original research."
- There is a CASA (Centre for Advanced Spatial Analysis) website linking a number of relevant papers and/or presentations, perhaps ca2003-2004, "Surnames as a quantitative resource: The geography of British and Anglophone surnames." I've looked over, in particular, the work by D. K. Tucker, "Surnames, forenames and correlations," but there are several relevant titles, "Surnames and the search for regions," "The how and why of a taxonomy of names," "Uneven patterns of emigration among the Anglophone diaspora."
As to that "relative" notion of surname concentrations, if you routinely conduct surname research in those indexed databases or record groups that you work with, then you probably do get a feel for how popular a surname was in a given area at that time. I was sort of introduced to genealogy/family history by my Miller line; so checking an area for the frequency a surname appears, particularly in census, is reasonably common in my own family work.
P.S. Will have to post another study as an update. I have been working through a pdf version of it on my hard-drive. Locating that article today has eluded me.
@Duncan I found your "other" question first and in answering that, I think I have dealt with this one. See https://genealogy.stackexchange.com/a/1794/70