Let $M$ be a module over a ring $R$. In nice situations (though I don't know what exactly nice means...) the following two numbers are equal:


1.) The codimension of the support of $M$


2.) The biggest $k$ such that $\text{Ext}^k(M,.)$ doesn't vanish


Why do we expect this intuitively? Why should lengths of injective/projective/flat resolutions have anything to do with the support?



Why do modules with small support have high Exts?
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. 


