Hardware Tips

In previous tips we discussed how to improve disk I/O performance. In the last tip, we looked at changing the partition offset and allocation unit size (a.k.a cluster size) of a drive. In this tip we look at how to re-benchmark to see if our changes made improvements.

During a recent SQL Server install everything seemed to go fine. SQL Server 2008 R2 went in with no problems. There was a local drive with a RAID 1 set for the OS and system databases and two direct attached RAID 5 sets; one for transaction log files and one for data files. Was everything okay? Could it be better? How could I tell? In this tip we look at how to maximize your IO throughput.

We've been discussing this for so long and warning Sys Admins about making sure our servers are in proper good nic before handing over to us for the SQL install and config.

Disk performance is critical to the performance of SQL Server. Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have. I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How does one set these parameters correctly? In this we show you how.

At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase. We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives. I also am interested in the rationale behind the decisions. Can you shed any light on the subject?

I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?

SQL Server performance is dependent on the server resources available and disk performance is probably the most important resource. To maximize disk performance for SQL Server, I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How do I find out the allocation unit size and partition offset for my drives?

Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?

During a recent SQL Server install everything seemed to go fine. SQL Server 2008 R2 went in with no problems. There was a local drive with a RAID 1 set for the OS and system databases and two direct attached RAID 5 sets; one for transaction log files and one for data files. Was everything okay? Could it be better? How could I tell? In this tip we look at how to maximize your IO throughput.

SQL Server performance is dependent on the server resources available and disk performance is probably the most important resource. To maximize disk performance for SQL Server, I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How do I find out the allocation unit size and partition offset for my drives?

At my company we are looking at purchasing a new SQL Server and I am not sure how many disk drives we should purchase. We do not have an unlimited budget because we are a small company, but I am interested in how we should configured disk drives for SQL Server if we bought 1, 2, 4, etc disk drives. I also am interested in the rationale behind the decisions. Can you shed any light on the subject?

Disk performance is critical to the performance of SQL Server. Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have. I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How does one set these parameters correctly? In this we show you how.

In previous tips we discussed how to improve disk I/O performance. In the last tip, we looked at changing the partition offset and allocation unit size (a.k.a cluster size) of a drive. In this tip we look at how to re-benchmark to see if our changes made improvements.

I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?

Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?

We've been discussing this for so long and warning Sys Admins about making sure our servers are in proper good nic before handing over to us for the SQL install and config.

In previous tips we discussed how to improve disk I/O performance. In the last tip, we looked at changing the partition offset and allocation unit size (a.k.a cluster size) of a drive. In this tip we look at how to re-benchmark to see if our changes made improvements.

Disk performance is critical to the performance of SQL Server. Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have. I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How does one set these parameters correctly? In this we show you how.

During a recent SQL Server install everything seemed to go fine. SQL Server 2008 R2 went in with no problems. There was a local drive with a RAID 1 set for the OS and system databases and two direct attached RAID 5 sets; one for transaction log files and one for data files. Was everything okay? Could it be better? How could I tell? In this tip we look at how to maximize your IO throughput.

SQL Server performance is dependent on the server resources available and disk performance is probably the most important resource. To maximize disk performance for SQL Server, I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How do I find out the allocation unit size and partition offset for my drives?

We've been discussing this for so long and warning Sys Admins about making sure our servers are in proper good nic before handing over to us for the SQL install and config.

Are solid state disk drives really the next big thing? Will they be able to expand the IO needs of an application further than the current set of disk drive technologies? Is this a technology I should consider with my future SQL Server platforms? The reason that I ask is because our SQL Server based applications are experiencing IO issues. We have split our databases into numerous file groups on numerous disk drives and have had some relief. At the same time we have been focusing on tuning our code and have made strides in that area. We just cannot tune the code quick enough to meet the demands. Are solid state disk something we should consider evaluating and migrating to in the near term to help with our performance issues?

I am a SQL Server DBA and I am very comfortable with the design and development side of SQL Server. I have been complimented by my own team on many of my database designs as well as on much of the behind the scenes development that I have done in SQL Server. Unfortunately, the tides have turned a little bit at work and we now have a stronger focus on the infrastructure components with some new people on the team. I know the basics about hardware, but I get lost on some of the newer terms that some of the new folks bring up. Can you help define some of the key hardware related items and the significance in SQL Server?