Let $ Y := Z^T \Sigma Z $. We have $ \operatorname{var}(Y) = \mathbb{E}(Y^2) - \mathbb{E}(Y)^2 $ and $ \mathbb{E}(Y) = \sum_i \sigma_{i, i} $. We thus need to compute $ \mathbb{E}(Y^2) $. For this, write
$$ \mathbb{E}(Y^2) = \mathbb{E}\left( \sum_{i, j, k, \ell} Z_i Z_j Z_k Z_\ell \sigma_{i, j} \sigma_{k, \ell} \right) = \sum_{i, j, k, \ell} \mathbb{E}\left(Z_i Z_j Z_k Z_\ell \right) \sigma_{i, j} \sigma_{k, \ell} $$
Due to $ \mathbb{E}\left(Z_i \right) = 0 $, the only non zero contribution of this last expectation comes from the indices such that $ i = j \neq k = \ell $ (plus the permutations of this case) or $ i = j = k = \ell $. In the first case, we have $ \mathbb{E}\left(Z_i^2 \right) = 1 $ and in the second case, $ \mathbb{E}\left(Z_i^4 \right) = 3$.
In the case $ \pi_1 := (i = j \neq k = l ) $ the sum becomes $ (\sum_{i \neq k} \sigma_{i, i} \sigma_{k, k} )^2 = (\sum_i \sigma_{i, i})^2 - \sum_i \sigma_{i, i}^2 $ and in the cases $ \pi_2 := (i = k \neq j = l) $ and $ \pi_3 := (i = l \neq j = k) $, it is equal to $ \sum_{i \neq j} \sigma_{i, j}^2 = \sum_{i,j} \sigma_{i, j}^2 - \sum_i \sigma_{i, i}^2 $. Summing, we get
$$ \mathbb{E}(Y^2) = 2 \sum_{i, j} \sigma_{i, j}^2 + \left( \sum_i \sigma_{i, i} \right)^2 - 3 \sum_i \sigma_{i, i}^2 + 3 \sum_i \sigma_{i, i}^2 $$
hence, the variance is
$$ \operatorname{var}(Y) = 2 \sum_{i, j} \sigma_{i, j}^2 $$
Concerning your question on the regime $ d \to \infty $, you can for instance suppose that $ \max_{i } \sigma_{i, j}^2 = o(1) $ to get $\operatorname{var}(Y) = o(d) $ (with the trivial inequality $ \sum_i \sigma_{i, j}^2 \leq d \max_i \sigma_{i, j}^2 $). You can also put hypotheses directly on the quantity of interest, namely $ \sum_{i, j} \sigma_{i, j}^2 $.
