Optimizing and Maintaining SQL and SharePoint Databases Part 2

One minute read

Session by Wayne Ewington & Neil Hodgkinson

[Live Blogging cross-post from engageinsharepoint.co.uk  at the International SharePoint Conference 2012 in London]

Mirroring Demo

  • Put your mirroring witness in a 3rd datacenter
  • When configuring mirroring, don’t forget to create the necessary logins & rights on the mirror server

Optimizing SQL for SharePoint

  • auto update create/update statistics is a no-no for use with SharePoint, despite what your DBA will tell you
  • maximum degree of parallelism (maxdop) should be set to 1, despite what your DBA will tell you
  • as maxdop is an instance setting, keep your sharepoint databases in a separate instance!
  • TempDB is king – watch out for contention
  • TempDB: presize it, high speed subsystem, separate from other lun’s, autogrowth fixed < 200 MB
  • Autoshrink is pure evil, don’t ever turn it on. Manual shrink only in very specific circumstances please.
  • Shrinking leads to fragmentation
  • Consider setting the minimum & maximum memory (e.g. both to 100% RAM - 4 GB)
  • GUID indexes lead to fragmentation (brakes read-ahead optimization)
  • Rebuild the index if is fragmented more than 30%
  • Check your databases frequently with DBCC CHECKDB but don’t use the repair option with SharePoint
  • Monitor for blocking transactions