Let (X, d, μ) be a metric measure space endowed with a metric d and a nonnegative Borel doubling measure μ. 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 pt(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 Lp bounds for Stein's square functions arising from Bochner-Riesz means associated to the operator L. We then use the Lp estimates on Stein's square functions to obtain a Hörmander-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 Schrödinger operators with rough potentials.