Good Best Practice Advice from the Pensacola Pre-Con, Round Two
Yesterday, I wrote about some advice that I received at the pre-con titled “Demystifying Database Administration Best Practices” which was presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog) from SQL Saturday #132 in Pensacola, Florida. Here are some more gems:
- Instant File Initialization is critical for performance, read more from Kimberly L. Tripp (twitter|blog).
- If you do not have Instant File Initialization enabled, then when you need to autogrow your database, it will have to zero out the file which may timeout the active query. However, the transaction log will always need to be zeroed out during an autogrow. These scenarios remind us to properly size our database during the planning stages.
- Page file sizing does not need to be one and a half times the amount of memory. Your SQL Server shouldn’t be paging, otherwise review your memory settings.
- Learn the principle of least privilege and apply it to your servers.
- Baseline new servers for performance so that you know whether your server is faster or slower. Do not just take a user’s word for it. For example, use SQLIO.exe against a new LUN to see your initial throughput.
- If you install SQL Server with the local system and decide later to use an active directory account to run the server, it will not delete the server principal name (SPN) and you will receive the “Cannot Generate SSPI Context” error.
- Also, if you change the account, be sure to use the SQL Server Configuration Manager instead of the Services applet.
Stay tuned for more items in our next installment. Enjoy!