Let $(X, d, \mu)$ be a metric measure space endowed with a metric $d$ and a nonnegative Borel doubling measure $\mu$. Let $L$ be a non-negative self-adjoint operator of order $m$ on $X$. Assume that $L$ generates a holomorphic semigroup $e^{-tL}$ whose kernels $p_t(x,y)$ satisfy Gaussian upper bounds but without any assumptions on the regularity of space variables $x$ and $y$. Also assume that $L$ satisfies a Plancherel type estimate. Under these conditions, we show the $L^p$ bounds for Stein's square functions arising from Bochner-Riesz means associated to the operator $L$. We then use the $L^p$ estimates on Stein's square functions to obtain a Hormander-type criterion for spectral multipliers of $L$. These results are applicable for large classes of operators including sub-Laplacians acting on Lie groups of polynomial growth and Schrodinger operators with rough potentials.