One of my main responsibilities at work is to keep the SharePoint database environment running. Just to give you an idea, I have three 64bit SQL Server 2005 Clusters and two load balanced SSRS servers.
SharePoint is an interesting application for a DBA to manage because Microsoft has some very rigid rules about how you can manage their databases. Basically to summarize, the SharePoint people say that accessing the database directly or changing anything on their databases aside from what's provided out of the box, etc. is not supported unless you do it thru the SharePoint API. This means that you can not add indexes or do "over the top" changes to fix issues. Therefore you generally need a SharePoint developer to write a power shell script when something goes wrong.
Bill Baer has released a white paper on the recommended strategies for the databases that host content and configuration settings for MOSS and WSS. I refer to this document as the "SharePoint Database Bible". If you ever have a Microsoft Health Check done on your MOSS farm, then one of the questions that the Premier Field Engineer will ask you is "Do you use the white paper for database maintenance?". Luckily, I stumbled upon the white paper before the visit so I was able to answer "Yes".
Overall the white paper is very well done. There are some settings that I originally did not agree with, such as setting the free space per page percentage on the rebuild index task to 70%. From a Microsoft Perspective I can see why they would recommend this. They basically want to minimize the amount of times that a page split occurs. I previously used a value of 90% on most of my maintenance plans but I did see the error in my ways and happily changed to 70%. Just as a warning, changing the free space per page percentage from a value of 90% to 70% will consume a lot of disk space for large databases. So before you make the change, validate the amount of free space you have available.
One of the most problematic experiences has been managing the cube build for our MS Project environment. To put it simply, the cube building process for Microsoft Project Server is a resource hog. I would highly recommended using a 64 bit box and buying lots of memory. Unfortunately, in our development environment we use 32 bit VMWare. To make matters worse, we have a single machine running SQL Server, SSRS and SSAS. The box initially was configured with 3 GB of RAM. Although the machine was a little slow things seemed to function OK. Over time as we added more data to the system we quickly discovered that the cube would no longer process. When diagnosing the issue I relied on two key performance counters:
- SQLServer:Buffer Manager\Buffer cache hit ratio - indicates how often SQL Server goes to the buffer, not the hard disk, to get data. In OLTP applications, this ratio should exceed 90%, and ideally be over 99%.If your buffer cache hit ration is lower than 90%, you need to go out and buy more RAM today. If the ratio is between 90% and 99%, then you should seriously consider purchasing more RAM, as the closer you get to 99%, the faster your SQL Server will perform. In some cases, if your database is very large, you may not be able to get close to 99%, even if you put the maximum amount of RAM in your server. All you can do is add as much as you can, and then live with the consequences.
- SQLServer:Buffer Manager\Page life expectancy - SQL Server Books Online (BOL) says the page life expectancy value is the "number of seconds a page will stay in the buffer pool without references." So, a buffer that has a 300-second page life expectancy will keep any given page in memory in the buffer pool for 5 minutes before the buffer pool flushes the page to disk—unless a process references the page.
Believe it or not the Buffer Cache hit ratio on my server was approximately 10 and my page life expectancy was floating around 12 seconds! This was obviously a memory issue. When I launched task manager and looked at the SQL Server process it was using 1.7 GB of memory. I knew I needed more so I called server operations and had them add another 1 GB of memory to make it a grand total of 4GB. In case you don’t know, this is an upper limit for a 32 bit box running Windows Server 2003 Standard Edition.
When the server came back online I opened up task manager again. I noticed that SQL Server was still only using 1.7 GB of memory. I scratched my head for a second and then I remembered the old /3GB switch. Here is a brief tidbit from Microsoft TechNet regarding how Server 2003 allocates memory:
Windows Server 2003 includes support for a startup switch that lets you tune the allocation of use of memory and memory address space. Regardless of the amount of physical memory in your system, Windows uses a virtual address space of 4 GB, with 2 GB allocated to user-mode processes (for example, applications) and 2 GB allocated to kernel-mode processes (for example, the operating system and kernel-mode drivers). On systems that have 1 GB or more of physical memory, these two startup switches can be used to allocate more memory to applications (3 GB) and less memory to the operating system (1 GB). This additional virtual address space helps reduce the amount of memory fragmentation in the virtual address space of the Exchange information store process.
The /3GB switch is used to effect this allocation change. The switch is entered in the system’s boot.ini file and takes effect after a restart.
So to make a long story short, I added the 3GB switch and rebooted the server. When I kicked off the cube build the SQL Engine maxed out to about 2.7 GB of memory and the cube finally processed successfully.
I guess the morale of this story is that the only supported way to tune a SharePoint database environment is to follow the SharePoint Database Bible and buy massive hardware. When planning out a MOSS deployment, push for 64 bit machines with tons of memory. When you have issues, make sure you are familiar with your perfmon counters.
