Chapter 8: Data Storage Design and SQL Server

This chapter will explore the most common storage technologies available to you. It will also present some benefits and concerns about different storage designs so that you can more easily choose what suits your solution. We will finish the chapter with a presentation of SQL Server-examining its architecture and how it fits into your storage policy.

Optimizing Performance

Now that you know how the internals of SQL Server work and how database design affects performance, we are going to introduce you to some performance tuning topics of interest.

Database Performance and I/O Configuration Options

Here we will discuss some of the performance counters you might want to keep an eye on to ensure your SQL Server is working like it should. When SQL Server reads data from tables, it uses Windows system I/O calls to perform this. SQL Server decides when and how this access is performed, but the operating system actually performs the work.

Disk I/O is the most frequent reason for performance bottlenecks, so you should not forget to monitor this activity on your server. As always, use System Monitor (or Performance MMC, as it is called in Windows XP/Windows Server 2003) to monitor your system. Figure 8-15 shows two performance counters:

• PhysicalDisk: % Disk Time, which is the percentage of time the disk is busy with read/write operations.
   
• PhysicalDisk: Current Disk Queue Length, which shows the number of system requests waiting for disk access.

Figure 8-15. Two performance counters that are used to monitor disk access

If the PhysicalDisk: % Disk Time value is above 90 percent, you should monitor PhysicalDisk: Current Disk Queue Length to see how many system requests are waiting in line for disk access. This value should be no more than 1.5 or 2.0 times the spindles making up the physical disk, so check out the hardware before counting. If both of these counters are consistently high, you probably have a bottleneck in your system. To solve this problem, consider using a faster disk drive or moving some files to an additional disk or even a different server. You could also add additional disks to your RAID system if you use one.

These counters are used if you only have one disk partition on your hard drives. If you have partitioned your disks, you should monitor the LogicalDisk counters instead of the PhysicalDisk counters (the names of the corresponding LogicalDisk counters are similar to those just mentioned for PhysicalDisk).

As you saw in Chapter 4, you should avoid paging on your server. To monitor this, use the Memory: Page faults/sec counter.

If you want to isolate the disk writes/reads that SQL Server is responsible for, monitor the following two counters (see Figure 8-16):

• SQLServer: Buffer manager page reads/sec
   
• SQLServer: Buffer manager page writes/sec

Figure 8-16. Two performance counters are used to separate SQL Server disk I/O from other I/O.

If the values for these two counters are near the capacity of the hardware, you should try to reduce them by tuning your databases or applications. You can do this by reducing I/O operations: Check your indexes so they are accurate or try normalizing your databases even more. If this still does not help, try increasing the I/O capacity of your hardware or adding more memory.

TIP If you find your bottleneck involves inserts and updates, you could try to normalize your data more. If you instead find that queries cause the bottleneck, you could try to denormalize the database. The problem with such a solution is that you might need to rewrite your applications to handle a new database structure. This might not be a problem if you discover this early in development, but in production this can be costly. Sometimes this might be the only way to correct the problem, however, and then you just have to deal with it.

You could monitor how much memory SQL Server is using by checking the value for SQL Server: Memory Manager Total Server Memory (KB). Compare this to how much memory the operating system has available by checking the counter Available Kbytes from the Memory object. This could give a clue to deciding if there is a need to restrict how much memory SQL Server is allowed to use. Next, we will move on to clustering SQL Server.