Tags: , , , | Posted by Andreas Vamvatsikos on 3/11/2011 10:13 AM | Comments (0)

Did you ever happen to administer, audit, develop or just use a SharePoint farm and face severe performance issues? Issues such as  extreme page load times, failed workflows, timer service complaining all over the logs?

Well I know there are several guidelines and techniques to fine-tune SharePoint performance but sometimes the cause is deeper. It might be that a poorly set-up or administered SQL Server can affect the farms performance to the point of rendering it unusable.

There are some very basic guidelines to setting up SQL Server correctly (in general not only for SharePoint) but you would be surprised how many times they are ignored, more so when a SharePoint dedicated SQL server is used. So consider these as a starting point:

· Separate the log files from the data files. Place each group on its own physical disk.

You can select a physical path for each database file when the database is first created by accessing the General Page of the New Database wizard, selecting the file and scrolling right to find the Path property. Press the “…” button to make your selection.

After the database has been created you can only assign paths to new files by right clicking on the database and selecting properties. Go to the Files page and press the Add button to add a new file (for which you will be able to set the path). Create for example a new log file on a different disk and phase out the existing log.

· Place the TEMPDB on a completely separate disk for optimal set-up.

· Manage the size of log and data files manually and preemptively. Don’t count on AUTOGROW.

 (This must also be applied to the TEMPDB and here (http://support.microsoft.com/kb/307487/ en-us) in an article in the MS Knowledge base describing how it is done).

· Make sure the SQL server process has enough allocated memory.

The 32bit SQL Server can use up to 2GB of RAM out of the box, but this can be increased. Right click on your server name in management studio, go to the memory page and enable AWE (“Use AWE to allocate memory”). You must restart the SQL instance for the option to take effect. This will get your SQL Server up to 4 Gigabytes of RAM. To go beyond that the OS must be able to provide more than 4GB RAM to the SQL Server Process. This is done by default in 64bit MS Server OSes. For 32bit MS Server OSes the /PAE switch has to be added to the boot.ini file so that the server can address more than 4 Gigs of RAM.

· The number of data files for the TEMPDB should be equal to CPU’s for best performance in intensive workloads. Split your TEMPDB up and SQL Server will handle the rest. (note: Dual Cores = 2 CPUs, Quad Cores = 4 CPUs)

· Make sure your disks (throughput and characteristics) can handle your SQL Server load.

· Use RAID1 (or 1+0) to boost write throughput. (RAID5 is also good but not so good).

· Install Service Packs and updates!

And that’s about it …. for starters. You have to be an SQL Server expert to dive deeper into to SQL Server fine-tuning but the above should be more than enough for your average SharePoint farm.

Add comment

  Country flag
  • Comment
  • Preview