News Stay informed about the latest enterprise technology news and product updates.

SharePoint performance demands finely tuned SQL Server, storage

You need proper storage, on-point SQL Server and 64-bit gear and settings to get the SharePoint performance you need.

McLEAN, VA. -- One interesting result of the swift corporate uptake of Microsoft Office SharePoint Server 2007 is that SharePoint administrators are meeting SQL Server database administrators and getting a crash course in storage technology for the first time.

The two groups have come to realize how much they need each other.

From the SQL Server database administrator point of view, it may be the first time he or she has needed to buy hardware that has performance as a primary concern.

More on SharePoint Server performance, SQL Server performance
Microsoft SharePoint Server 2007 backup and disaster recovery tutorial

Determining SQL Server database storage requirements

Want to learn more about Microsoft SharePoint? Sign up for our new SharePoint newsletter and get tips and tricks on performance, governance and more.
"SharePoint lives on SQL Server, so if it isn't done right you can expect trouble with performance," said Dennis Martin president of Denmartek, a consulting firm in Arvada, Colo., speaking at the Best Practices SharePoint Conference held here this week.

One SharePoint administrator for an educational foundation based in Washington, D.C., who declined to be identified, said every database search means crawling everything, reading and writing, so it stands to reason that 32-bit technology does not provide enough memory.

"In my case, the SQL Server administrator made a hardware request and got an 8-processor, 32-bit box," he said. "After a long process we learned you need to have that 64-bit memory, but the SQL administrator just went back and bought more 32-bit boxes."

For the SharePoint administrator new to storage and a SQL Server newbie, Martin offered tips on how to get better SharePoint performance.

For SharePoint performance, don't skimp on storage

Apart from keeping SQL Server tuned for high performance, the other crucial aspect for running a proper SharePoint site is using the right storage.

Martin said for SQL Server and SharePoint installations, he only considers enterprise- or desktop-class disk drives. Enterprise drives have higher rotation speeds and dual processors. They have two forms of command queuing and can tolerate higher vibration in racks. They are also designed to be powered up and spinning, all day, every day.

Desktop drives have slower rotation speeds and they are single processor. They are also less expensive than enterprise drives and have shorter warranties. But they are better than other low-end options.

He recommended RAID 10 technology for boosting SQL Server performance because it spreads data over multiple spindles versus using individual disks. A second choice would be RAID 5 for best capacity.

With regard to storage architectures, Martin offered SharePoint administrators a primer on direct-attached storage (DAS), storage area networks (SAN) and network-attached storage (NAS).

He recommends using DAS for simple applications requiring only one server. For file serving and sharing over TCP/IP, use NAS. And he said to use a SAN to share storage between servers and to ease reallocation of storage as needs change and grow.

Some SQL Server best practices

Martin said databases should be configured with performance and availability as design criteria. This means using more disks and faster disks for the best performance. Again, "the more spindles you have the better," he said.

He also recommended placing TempDB, a SQL Server database, on its own RAID set. "It's very write heavy, so put it on the fastest thing possible," he said. "Microsoft best practices say the number of TempDB data files should equal the number of CPU cores in the server."

Martin also told administrators that if they are running Windows Server 2003 or older, they must align the file system to the disk offset recommended by the storage vendor. If that value is unknown, then use an offset of 64 kb. Without this volume alignment it's likely there will be performance problems, he said.

Martin said that in Windows Server 2008 there is a default alignment of 1 MB, so, "this problem has been fixed."

Dig Deeper on SharePoint administration and troubleshooting

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.