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:

  1. Instant File Initialization is critical for performance, read more from Kimberly L. Tripp (twitter|blog).
  2. 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.
  3. 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.
  4. Learn the principle of least privilege and apply it to your servers.
  5. 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.
  6. 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.
  7. 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!


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on June 12, 2012, in SQL Saturday and tagged , . Bookmark the permalink. 2 Comments.

  1. So what *is* the best practice on page file size? Any pointers to authoritative articles?

  2. I’ve always operated on the rule of thumb that says 1.5 times the amount of RAM.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: