Of course in many cases (such as the one you mentioned), the amount of memory needed does not grow with N. But there are other programs/computation that seem to require more memory. For example, consider the following (Python) program:

    def intpow(n, r): return int(n**r)
    N = 10000
    a = [0] * N
    a[1] = 1
    for m in range(2, N):
        a[m] = a[m-1] + a[intpow(m, 0.6)] + a[intpow(m, 0.3)] + a[intpow(m, 0.1)]
    print(a[N-1])

It populates an array of size N by, for each index m, accessing array elements at smaller values m-1, m^0.6, m^0.3 and m^0.1 (just arbitrary values I made up). So this is a program that runs in time about N, and as it can depend on array elements that go very far back, it may not be immediately obvious how to make it run with space significantly less than N^0.9 (say), let alone √N. But the result shows that it can be done (disclaimer: I haven't actually verified the details of how the proof carries over to this program, but it looks like it should be possible), and for any program no matter how weird.