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!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, 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:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: