To understand what "nice" is in your sense has been a very interesting question in commutative algebra.

In the following discussion I will assume, unless otherwise notice, that $(R,m,k)$ is Noetherian local, and $M$ is finitely generated. Let $(1)$ be the codimension of support of
$M$ and $(2)$ be the biggest non-vanishing index of $\text{Ext}(M,-)$.

First, the number (2) is finite forces $M$ to have finite projective dimension by taking $N=k$ the residue field. So we will assume $\text{pd}\ M <\infty$. Then, as BCrd pointed out:

$$ (2) = \text{pd} \ M = \text{depth} \ R - \text{depth} \ M$$

The first inequality is easy by computing Ext via a projective res. of M + Nakayama lemma. The second is the Auslander-Buchsbaum theorem.

On the other hand:

$$(1) = \text{dim} \ R - \text{dim} \ M $$

So

$$(1) - (2) = (\text{dim} \ R - \text{depth} \ R) - (\text{dim} \ M-\text{depth} \ M) $$

Thus, if both $R,M$ are Cohen-Macaulay (which by def. means dim=depth) and $\text{pd}\ M <\infty$ then $(1) = (2)$. If $R$ is "more Cohen-Macaulay" then $M$, we will have $(1)<(2)$.

The situation described in Emerton's answer is also very interesting. In general, the smallest index for which $\text{Ext}^i(M,N) \neq 0$ is the biggest length of an $N$-regular sequence inside the annihilator of $M$. When $N=R$ this number is called the *grade* of $M$, which I will call (3).

It is easy to see that $(3) \leq (1)$ in general. One can prove that $(1) = (3)$ if $R$ is Gorenstein as follows: By Local Duality, $\text{Ext}^i(M,R)$ is Matlis dual to the local cohomology module $\text{H}_{m}^{d-i}(M)$, here $d= \text{dim}\ R$. It is not hard to show that local cohomology vanish beyond $\text{dim}\ M$, QED.

Amazingly, it has been an open conjecture for 50 years that $(1)=(3)$ whenever $M$ has finite projective dimension!

For "intuitive" understanding, I would offer the following: often when study modules of finite projective dimension one draw inspirations from those of the form $R$ modulo a regular sequence (so the resolution is a Koszul complex). In such case one can easily see that $(1) = (2) =(3)$.

EDIT: I got too caught up in the results and forgot your main question: why bigger codimension implies bigger projective resolution? A very low-tech way to see it is: bigger codimension means bigger annihilator of $M$. Now each element of the annihilator of $M$ gives a non-trivial relation on elements of $M$, namely $ax=0$, so it is not surprising that the modules with bigger annihilators have more complicated resolutions.

finite. Theproofof Aus.-Buchs. formula is via induction on the mysterious proj.dim.(M) (assumed finite!) using regular sequences. So there's an answer: "nice" is related to "long" regular sequences, and look at how they're used in that proof. – BCnrd May 23 '10 at 12:30