What is the spectral theorem for compact self-adjoint operators on a Hilbert space actually for?
Solution 1:
What is the spectral theorem for compact operators good for? Here are some examples. (I am ignoring the self-adjoint aspects, since they don't really play a role in the theorem. And it is valid for more general spaces than Hilbert spaces too, so I will also ignore that part, in the sense that I won't pay too much attention to whether my examples deal with Hilbert spaces on the nose, rather than some variant.)
Proving the Peter--Weyl theorem.
Proving the Hodge decomposition for cohomology of manifolds (using the fact that the inverse to the Laplacian is compact); Willie noted this example in his answer too.
Proving the finiteness of cohomology of coherent sheaves on compact complex analytic manifolds.
In its $p$-adic version, the theory of compact operators is basic to the theory of $p$-adic automorphic forms: e.g. in the construction of so-called eigenvarieties parameterizing $p$-adic families of automorphic Hecke eigenforms of finite slope.
It is also a basic tool in more classical problems, such as the theory of integral equations. (It is in this context that the theory was first developed; see Dieudonne's book on the history of functional analysis for a very nice account of the historical development of the theory.)
Solution 2:
Maybe a definition of functional calculus? Using quantum mechanics notation, if $A$ is self-adjoint and compact, then $A = \sum \lambda_k |k\rangle\langle k|$, which means that for $f:\mathbb{R}\to\mathbb{R}$ we can define $f(A) = \sum f(\lambda_k) |k\rangle\langle k|$.
This allows us to give a simple demonstration of Stone's theorem for such operators: that $\exp itA$ is a strongly continuous one-parameter unitary group on your Hilbert space.
It also gives very simple motivation for the construction of Green's functions and resolvent operators.
Besides the usual quantum harmonic oscillator, a similar construction can be used to give the decomposition of $L^2$ via eigenfunctions of the Laplacian on a compact manifold. This naturally leads to the Hodge decomposition, which I'm told is generally considered to be somewhat useful :-)
That on a compact manifold, the inverse of the Laplacian is a compact operator means that, discarding the harmonic functions, the Laplacian has a lowest eigenvalue. This fact (and that self-adjointness allows it to be diagonalized) allows you to define the Zeta-function determinant of the Laplacian using some analytic continuation tricks. On 2-dimensional closed surfaces, this is an interesting invariant with nice geometric properties.
(Note that in the case of non-compact domains, the inverse Laplacian is no longer a compact operator, and the Laplacian has a continuous spectrum. So the summation in the zeta-function determinant no longer makes any sense...)