Periodic silver coated hydrogen silsesquioxane nanopost arrays (HSQ@Ag NPAs) with various diameters were fabricated as surface-enhanced Raman scattering (SERS) active substrates, and the SERS performance of the substrates were studied both experimentally and numerically. Raman signals of Rhodamine 6G molecules absorbed on the HSQ@Ag NPAs were measured and showed excellent SERS performance with significant enhancement and high uniformity. The enhancement factor under 514.5 nm excitation wavelength increased firstly and then decreased, but increased monotonically under 633 nm excitation wavelength. Finite-difference time-domain simulations of electric field distribution and far field absorption demonstrated that SERS enhancement is related to strong electric enhancements under both excitation and Stokes wavelengths, and the strongest enhancement occurred in HSQ@Ag NPAs with localized surface plasmon resonance (LSPR) wavelength located in the region between excitation wavelength and Stokes wavelength. Thus, by tuning the LSPR wavelength to the region between excitation wavelength and Stokes wavelength via reasonably designing the parameters of the nanostructure, SERS substrate with excellent performance could be obtained. Our work could be helpful in understanding the fundamental mechanism of SERS and provides a possible way to reasonably design excellent SERS substrates.