When to use the word "Prof." in an SOP? When should I call a professor in my SOP as Prof. X, or just leaving their name as X? All I want is not to be disrespectful, but also not to make unnecessary repetition. 

I think the natural way to call them is based on the context: when I mention them as an author of a paper, then X is fine, but when mentioning them as my future advisor, then I should call them Prof. X. However, I'm worried that will make inconsistent in calling their name. Also, can I only mention their title at the first time and just writing the name for the rest?

Moreover, in my situation, X has retired and recommend Y who is from outside the school I apply. Y agrees to co-advise me with Z who is an official faculty* of the school. I want to mention this in my SOP, but wonder if I should write their titles in. They are all professors, however Y doesn't mention the titles in the emails, so I don't think there is a need to mention them.

*Btw, what is the good name for this term?
100% opinion-based and anecdotal.

I (and I am intentionally not saying "People" here though I do not think that would be an overstatement) will feel very weird if a student refers to a teacher with whom he/she has a professional relationship using only last name. So, I agree with Jonas's comment that you should use "Prof." all the way through.

In general social settings, I have only seen last name-based communication in sport teams (my coach called us by last name, and more than usual we teammates referred to each other with last name), and when someone particularly requested me to do so (e.g. "Nice to meet you, I'm Bruce Wayne; I go by Wayne.")

In academia, referring to someone with last name usually implies you do not personally know that person, not even by the 2nd degree. If I were to refer to a teacher in my school whom I do not know, I'd still say something like, "Hi, this is my friend John, he studies with Prof. Buddy" even I don't know the professor personally. One of the exceptions would be when a possessive is involved, like "Buddy's laboratory" or "The Dumbledore's Army."

When I refer to someone more remote, the last name method is usually used, e.g. "Have you read that paper by Kardashian? Sick stuff." Or, the full name method: "John Tukey introduces box plot in his book Exploratory Data Analysis." I wouldn't call them Prof. Kardashian or Dr. John Tukey. And yet, if I get to speak to a former student of John Tukey's, I would perhaps ask "So, how was it like studying with Dr. Tukey?"

As for worrying about repetition, out of all possible grammatically annoying repetitions, this is nothing worth concerning. In fact, it's more annoying to read a letter from a student who sometimes refers to the same person as "Prof. Y" and then flips to "Y." That sounds mildly psychotic.

I'd conclude that, yes, it is a big mess. English itself comes with plenty of odd rules and exceptions. Compounded with cultural practices and manners it can be confusing. It's not my primary language and the only way to get around it would be to listen and read more. And if you need to pick one in a pinch, err on the more polite and formal expression.