Scaling Up

Much of this chapter has consisted of talking about scaling out using DPVs. Microsoft until recently favored scaling out, as in a web farm type environment. Recently, though, with the advent of 64-bit architecture and the price of 16-way processor machines dropping incredibly fast, Microsoft has slightly adjusted their scaling strategy to look more into scaling up. The 64GB limitation on SQL Servers will be a thing of the past once the 64-bit chipset begins to explode in the corporate environment, and the Windows platform will begin to look much like the mainframe world.

In the meantime, while you're waiting for the real scaling-up picture to unfold, you'll have to do a little configuration legwork to get SQL Server to scale (Windows is really the limitation). By default, SQL Server can only access 2GB of RAM (the number will appear as roughly 1.7GB). To allow Windows to see the additional RAM, you must first enable the /3GB switch in the boot.ini file (located on the drive where Windows is installed), as shown on the last line in the following code:

 [boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(2)\WINNT [operating systems] multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Advanced Server" /3GB /fastdetect

(Note that the last two lines have wrapped, but should actually be on one line.)

If you have more than 4GB of RAM, you must also enable the /pae switch in the same boot.ini. Table 9-4 shows you a little more about how to configure the boot.ini and SQL Server to address the additional RAM. I discussed configuration of SQL Server for AWE in Chapter 2.

Table 9-4: Configuring Windows Memory for Scaling Up

4GB or Less

4-16GB

More than 16GB

/3GB switch enabled in boot.ini

/3GB switch enabled in boot.ini

/3GB switch disabled in boot.ini

 

/pae enabled in boot.ini

/pae enabled in boot.ini

 

AWE enabled in SQL Server with sp_configure

AWE enabled in SQL Server with sp_configure

Scaling with Multiple Instances

In certain instances, it may make sense to scale and consolidate servers with multiple instances of SQL Server on one machine. One such instance is when you have a database or set of databases that highly utilize procedure cache, which is where SQL Server caches query plans. A single SQL Server instance can only utilize 2GB of procedure cache. If you run multiple instances of SQL Server on a single server, you can increase the amount of procedure cache you can use.

Note 

If you're running Enterprise Edition of SQL Server, you will not be charged additional licensing cost for the additional instances.

By installing another instance on your server and distributing your databases across two instances, you will receive 4GB of procedure cache across both instances (assuming you have the RAM). If you decide to take this route, you can optimize this type of environment by performing some simple configuration changes. Another reason for this scaling method is when your system has an active tempdb. Since each instance owns its own tempdb database, you can scale outward with instances that way.

Processor Affinity

SQL Server encounters quite a bit of overhead when it has to juggle requests across many active processors and many instances of SQL Server. To relieve that type of pressure, it's best to dedicate a certain amount of processors per machine. This way, each instance of SQL Server can manage its own processor and the operating system does not have to pass cross-instance instructions across the processors. To set this type of affinity, go to each instance's properties in Enterprise Manager under the Processor tab and check what processors you'd like SQL Server to use.

Tip 

You may experience up to a 75 percent performance enhancement by setting SQL Server to not share its processors in a multi-instance environment.

Memory

I've found that there's no performance benefit between dynamic and static memory configuration. In a multiple instance environment, always set a floor and maximum memory setting, though, so one instance doesn't kill another instance's memory. A memory floor is also nice because it makes SQL Server grab the necessary amount of memory faster than it would normally take.

Disk and Recovery Models

Ideally, you'd want each instance to have its own set of drives so it does not have to compete for disk I/O. You may also see a performance benefit from using the Full recovery model versus Bulk-Logged. In my tests, I experienced about 7.5 percent better performance with 200 databases across multiple instances by using the Full recovery model.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net