MathOverflow will be down for maintenance for approximately 3 hours, starting Monday evening (06/24/2013) at approximately 9:00 PM Eastern time (UTC-4).

In the following discussion I will assume, unless otherwise notice, that $R$ (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. 2 added 288 characters in body 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$is Noetherian local, and$M$is finitely generated. 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)$. 1 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$is Noetherian local, and$M$is finitely generated. 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!