This response is in answer to David's further question about whether it is possible to bound the rate at which SN/N tends to zero, as he was wanting to use Weyl's inequality to do. This is not possible, even in the case d=2 and f(n)=θn2. (for d=1 it is not hard to show that SN is bounded so $S_N/N=O(N^{-1})$). Set $$ S_N(\theta)=\sum_{n=1}^Ne^{2\pi i\theta n^2} $$ in the following. Given any function h: ℕ → ℝ+ with liminfnh(n) = 0, I show that there are irrational θ with $$ \begin{array}{}\displaystyle\sup_N\vert S_N(\theta)/(h(N)N)\vert=\infty.&&(*)\end{array} $$
[Note: The following is a much simpler argument than the original version]. I'll use the Baire category theorem to find counterexamples
For any countable collection An of open dense subsets of ℝ, the intersection A = ∩nAn is dense in ℝ.
In particular, any such A is nonempty. We can say more than this; if S is a countable subset of the reals then $A\setminus S=\left(\bigcap_nA_n\right)\cap\left(\bigcap_{s\in S}\mathbb{R}\setminus\{s\}\right)$ is an intersection of dense open sets, so is dense. In particular, A will contain a dense set of irrational values.
To construct counterexamples then, it is only necessary to show that the set of all θ at which the sequence diverges to infinity is an intersection of countably many open sets, and show that it contains a dense set of rational numbers. The Baire category theorem implies that it will also diverge at a dense set of irrationals.
In fact, for any sequence xn(θ) depending continuously on a real parameter θ, the set of values of θ for which it diverges to infinity is an intersection of countably many open sets $$ \{\theta\colon\sup_n\vert x_n(\theta)\vert=\infty\}=\bigcap_n\bigcup_m\{\theta\colon\vert x_m(\theta)\vert>n\\}. $$$$ \{\theta\colon\sup_n\vert x_n(\theta)\vert=\infty\}=\bigcap_n\bigcup_m\{\theta\colon\vert x_m(\theta)\vert>n\}. $$
So, we only need to find a dense set of rational numbers at which (*) holds.
Let θ = a/b for integers a,b with b > 0. Setting $x=S_b(\theta)/b$ then $S_N(\theta)/N\to x$ as $N\to\infty$.
Proof: If m ≡ n (mod b) then θm2 - θn2 is an integer, and $e^{2\pi i\theta m^2}=e^{2\pi i \theta n^2}$. So $n\mapsto e^{2\pi i\theta n^2}$ has period b, giving $$ S_{bN}(\theta)=\sum_{j=0}^{N-1}\sum_{k=1}^{b}e^{2\pi i\theta(jb+k)^2}=N\sum_{k=1}^be^{2\pi i\theta k^2}. $$ So, SbN(θ) = NSb(θ). Now, any N can be written as N = bM + R for some R < b. Then, $\vert S_N-MS_b\vert\le R$ and, dividing by N gives $\vert S_N/N-S_b/b\vert\to0$ as N goes to infinity.
As |SN(θ)/(h(N)N)| ∼ |x|/h(N) → ∞ whenever x is nonzero, the following shows that (*) holds whenever θ is of the form a/p for an odd prime p not dividing a. Such rationals are dense, so the existence of irrational θ for which (*) holds follows from the Baire category theorem.
Let θ = a/p for integers a,p with p an odd prime not dividing a. Then $x=S_p(\theta)/p$ is nonzero.
Proof: Note that $u=e^{2\pi i a/p}$ is a primitive p'th root of unity with minimal polynomial $X^{p-1}+X^{p-2}+\cdots+X+1$ over the rationals. Then, all proper subsets of $\{1,u,u^2,\ldots,u^{p-1}\}$ are linearly independent over the rationals and $$ S_p(\theta)=\sum_{k=1}^{p}u^{k^2}=1+2\sum_{k=1}^{(p-1)/2}u^{k^2} $$ is nonzero.
In fact as pointed out by David belowbelow, Sp is a Gauss sum and has size √p.