Administration and Performance

Initial File Initialization

This is an interesting area and can significantly impact the performance of the following: create new database, grow database file, auto expand, restore backup. Rather than repeat what others have said the following articles summarise this:

• 'Instant Initialization - What, Why and How? - this gives a good overview of what impact this can have
• How and Why to Enable Instant File Initialization - SQL Server Premier Field Engineer Blog - this is the best article I have found on how to enable it and test it is working
• Database File Initialization - this is a SQL Books Online article
• Misconceptions around instant file initialization - this deals with some common misconceptions

One important point to note is that the Local Administrators group on the server already has this permission. Therefore if your SQL Server instance is running under an account that is a member of the local Administrators group then no changes are required. However this would not be considered best practice.

It has to be said I do not know if this is still an issue in SQL Server 2012 with Windows Server 2012, however it is definately an issue on SQL Server 2008 R2 and Windows Server 2008 R2.

Memory

It is generally best to set the maximum memory that SQL Server can use. If you have SQL Cluster or multiple instances then your logic might vary but in the single, standalone instance scenario then the following blog post seems good: Tibor Karaszi : Setting max server memory. The added bonus is you can run it on the server in question and get some handy SQL output to run!

So, this begs the question, is setting maximum memory that simple? The short answer as usual is "no, it depends"! This is basically to do with what else is on the box, what the box is used for and how big the databases are. If you're whole database is less than max_memory then it's unlikely usage will get as high as max_memory. If users keep logging in and running applications then the OS and these applications will need more memory, likewise if you have applications or services running. What I would probably do is break out Perfmon Target and Total Memory counters, to see where they sit over a period of time:

• MSSQL$<instance_name>:Memory Manager\Total Server Memory (KB) - Total amount of dynamic memory the server is currently consuming
• MSSQL$<instance_name>:Memory Manager\Target Server Memory (KB) - Total amount of dynamic memory the server is willing to consume

Things to look out for:

• low Total compared to Target
• flutter - ie Target goes up and down around it's boundary, indicating SQL is giving memory, getting memory repeatedly
• flat-line Target = Total or flat-line Total
• any other memory-hungry apps / .exes on the server will eat into this

Hopefully that helps, however I owe a friendly SQL Server DBA credit for this advice!