Check instant file initialization and lock pages in memory options
Study your server reports do the work for you! Instant file initialization and lock pages in memory are 2 windows operating system options that can help us improve our overall SQL Server performance. These options are already checked in the Server Dashboard report available in my free tools . We will talk about both options and how to check them. Note that these options are for SQL Server running under Windows OS, for Linux systems you can ask Carlos Robles via his webpage or twitter. He has a lot of experience working with databases (SQL Server, Oracle, MySQL, Sybase) running under UNIX environments. Instant file Initialization When you create any file on the operating system, any data left in disk is overwritten by filling with zeros all the file, that could take some time depending of the file size. For SQL Server this is done when you restore a backup, perform an auto growth operation, create a data/log file (if you put an initial value of several GB this zeroing