If $X$ is noetherian of dimension $>0$, there is always a compact object of $SH(X)$ which is not dualizable: e.g. $j_\sharp$ of the sphere spectrum where $j:U\to X$ is any dense open immersion with non-empty complement (in each connected component). However, smooth and proper $X$-schemes always provide a source of dualizable objects. In fact, $f_*$ preserves dualizable objects for any smooth and proper map $f:Y\to X$ (this is a consequence of relative purity, of the projection formula, and of proper base change).
To see how few dualizable objects there are, it is enlightening to look at classical sheaves. Let $X$ be a topological space, and $D(X)$ be the derived category of sheaves (of complexes of abelian groups, or of spectra...) over $X$. Then the dualizing objects of $D(X)$ are precisely the locally constant sheaves with values in perfect complexes. For schemes over $\mathbf{C}$, the Betti realization functor
$$SH(X)\to D(X(\mathbf C))$$
commutes with the six operation, hence, in particular, with tensor product, and therefore preserves dualizable objects. This means that any sheaf on $X(\mathbf C)$ which is not locally constant and which is of geometric origin must provide an idea of how to construct a non-dualizable compact object of $SH(X)$. If you want to work over a more general base, you may replace $D(X(\mathbf C))$ with other target of realizations ($\ell$-adic sheaves, or arithmetic $D$-modules).
