Getting optimal performance with Windchill depends on how the logical drives are configured and delegated. Randomly assigning logical drives will not give optimal performance. Having too few logical drives will result in greater latency and reduced performance.

When you are about to install Windchill, to move it to a new computer, or to rebuild an existing computer, you can significantly improve performance by running the SQLIO Disk Subsystem Benchmark Tool on your logical drives to test the drives' characteristics. A logical drive is the top layer of a pyramid of hardware or software on physical drives. The physical basis may be a SAN or a RAID array of drives. Regardless of the hardware or software combination that was used to create a logical drive, the logical drive must provide data redundancy through mirroring or parity.

A Single Physical Spindle

To understand why using a single drive is bad for performance, assume that you have one spindle drive (thus one drive head), and then look at what happens. Imagine that you create and fill a temporary table and then you copy the data to another table, such as an audit table. This is what happens:

  1. The head moves to the track that contains the database table and waits for the data to spin around. The data is copied into memory.
  2. The head moves to the track that contains tempdb and writes the data.
  3. The head moves to the track that contains the tempdb transaction log and writes the log.
  4. The head moves to the track that contains tempdb and reads the data.
  5. The head moves to the track that contains the transaction log table and writes the data.
  6. The head moves to the track that contains the transaction log and writes the log.
  7. The head moves to the track that contains tempdb and deletes the data.
  8. The head moves to the track that contains the tempdb transaction log and writes the log.

So, you had eight head movements at 5,200 RPM. If you assume the worst case—that when the head moves to the right track, the data has just passed—you end up with 60 seconds / 5,200 RPM * 8 heads = 0.092 seconds: 1/10 of a second for this action. The delay due to moving heads to get to data is called latency. Thus, 1/10 of a second of latency is assuming that no other users or programs are trying to access the drive.

Multiple Logical Drives

If you want good performance, you want to reduce latency and ideally have the data on the disk in the primary key sequence so that the data is read sequentially and quickly. The best way to avoid latency is to have similar items, especially logs that are sequential and easily streamed to the disks, on different drives.

Windchill is installed with the following four filegroups:

  • PRIMARY
  • BLOBS
  • INDX
  • WCAUDIT

PTC has done its homework so you can get good performance. For the best performance, each of these filegroups should be on its own logical drive. In addition to these four filegroups, you should also have separate logical drives for the following:

  • tempdb
  • tempdb log file
  • wcAdmin database log file

Additionally, if you are doing backups while the database is in operation, the backup drive should be on a separate logical drive. Have you lost count of the number of logical drives? The number is eight. But, you also have the operating system and its virtual memory paging file, which, fortunately, you can typically double up with the above. By assigning each of the above to a different logical drive, you reduce latency and improve performance:

  • SQL Server is reading through an index (on INDX) to get the pointers to records to be checked out.
  • SQL Server in parallel is reading and updating the records (on PRIMARY).
  • SQL Server in parallel is reading the large documents that are associated with the records (on BLOBS).
  • SQL Server in parallel is recording this checkout in the wcAdmin audit log (on WCAUDIT).
  • While all of this is happening, the transaction log is being written to in parallel.
  • If you need to use tempdb, that is also occurring in parallel.

Why is there a difference of performance? If there is only one head available, data reads and writes must be serialized, which causes latency as the head jumps around on the physical drive. With independent physical drives, operations may occur in parallel. In many commercial products, you will not see the same excellent preallocation of items to different filegroups that you see in Windchill. You can use these filegroups to leverage performance by just supplying drives to allow parallel operation.

You can see that you need eight logical drives. You also need to assign the above files (filegroups, backups, etc.) to different drives. How do you do this for performance? First, you need to understand that some items are random access (read/write) and some items are sequential writes.

Random

Sequential

INDX

WCAUDIT

PRIMARY

wcAdmin transaction log

BLOBS

tempdb transaction log

tempdb

Backup

All drives do not perform the same, even when they are identical and are from the same lot due to differences in physical components and quality control. We need to test them. Fortunately, Microsoft provides a free tool that is perfect for this job: the SQLIO Disk Subsystem Benchmark Tool. You can download this tool from the Microsoft Download Center at http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en.

For more information about using this tool, visit the following websites:

I will assume that you have measured the random read/write speed and the sequential write speed on all of your drives. So, how do you assign them? The following heuristic should result in a good assignment:

  1. Assign the fastest random read/write to tempdb, otherwise it will bottleneck the reading of data to it from other drives. (You want to avoid data arriving faster than it can be handled.)
  2. Assign the fastest sequential write to the tempdb log. You cannot do things faster than you can log them!
  3. Assign the fastest remaining sequential write to the wcAdmin log. You cannot change the data in the database faster than you can log the changes.
  4. Assign the fastest remaining random read/write to INDX. A record cannot be changed until all of the indexes associated with it are updated.
  5. Assign the fastest remaining random read/write to PRIMARY. Typically, these are short records that are often changed.
  6. Assign the fastest remaining random read/write to BLOBS. Typically, these are large files that are read/written sequentially from the disk. (You may want to run a test for sequential read/write on the drives if time permits.)
  7. Assign the fastest remaining sequential write to the WCAUDIT log. The audit log is used less than either the wcAdmin log or the tempdb log.
  8. Assign the fastest remaining sequential write to the backup. Backups are done by sequential writing to disk.

Summary

The bottom line is that getting optimal performance with Windchill depends on how the logical drives are configured and delegated. Reducing the number of logical drives to fewer than eight will reduce performance.

Do not get physical drives and logical drives confused. A logical drive should consist of two or more independent physical drives (to provide redundancy) with write buffering disabled on the physical drive, the drive controller, and the operating system.

Ken Lassesen is part of the original team that created Dr. GUI of MSDN and specializes in new and resurrected commercial product architecture. He developed architecture for several Microsoft websites, including the original MSDN site and the current Microsoft Partner Network site. He's equally at home with SQL Server, XHTML, Section 508 accessibility standards, globalization, Security Content Automation Protocol (SCAP) security, C#, and ASP.NET server controls. When he is not having fun with technology, he enjoys taking lunch-break hikes in the North Cascades.