The general consensus is that Sheol/Gehinom/Hell is a place the soul is sent to post-mortem for a Purgatory-like cleansing for up to 12 months. According to Nachmanides in Sha'ar HaGemul quoting Pirkei D'Rabbi Eliezer, She'ol is the lowest level of Hell, also known as Gehinom. He also has the best description of what it is and is cited by pretty much everyone else. I wrote a paper about this for an English class in college - here's a rough translation of the section describing Gehinom (feel free to correct as necessary - I don't have the Sefer on hand):
There are seven levels of Gehinom: Gehinom, Sha’arei Tzalmavet, Tit HaYavein, Bor Shachat, Avdon, and Sheol. In each level of Gehinom there is a major sinner in Jewish History and ten nations: Avshalom (Absalom) in Gehinom, Doeg (Doeg) in Sha’arei Tzalmavet, Korach in Tit HaYavein, Achav (Ahab) in Bor Shachat, Micha (Micah) in Avdon, Elisha ben Avuya in Sheol. In addition to Gehinom, there is a lesser known area below Gehinom called Arka where a small percentage of sinners go. (Arka is similar to Hell in that sinners never leave. However, it differs in that sinners burn eternally in a river of fire, rather than suffer different punishments for different sins.)
From what I recall, it's very difficult to end up in Arka. However, everyone - whether Jew or Gentile - goes through some level of Gehinom before going to Olam Habah.
I know there is a section in Eruvin about it as well, but that discussion is about where the physical entrances to are (whether you can take that literally or not is a discussion for a different time). Derech HaShem and Gesher HaChaim cite both the Ramban and the Gemara in Eruvin.
I hope that helps.