You are previewing SQL Server Hardware.
O'Reilly logo
SQL Server Hardware

Book Description

Relational databases place heavy demands on their underlying hardware and many of these databases are mission-critical resources for multiple applications, where performance bottlenecks are immediately noticeable and often very costly to the business. Despite this, many database administrators are not very knowledgeable about server hardware. Many medium-to-large companies have completely separate departments that are responsible for hardware selection, configuration, and maintenance, and the DBA with no knowledge of hardware is often completely at their mercy. Likewise, many DBAs are also unaware of the performance implications of the various options and configurations for SQL Server, and the Operating System on which it is installed. Glenn Berry is a highly experienced Database Architect, teacher and SQL Server MVP, and his book is designed to provide the fundamental knowledge and resources you need to make intelligent choices about optimal installation and configuration of SQL Server hardware, operating system and the SQL Server RDBMS. With what you learn in this book, you'll be able to ensure that your SQL Server instances can handle gracefully the CPU, memory and IO workload generated by your applications, and that the operating system and SQL Server itself are installed, patched, and configured for maximum performance and reliability.

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Introduction
    1. Who is this book for?
    2. How is the book structured?
    3. Code examples
    4. Other sources of hardware information
  5. 1. Processors and Associated Hardware
    1. SQL Server Workload Types
    2. Evaluating Processors
      1. Cache size and the importance of the L2 and L3 caches
      2. Clock speed
      3. Multi-core processors and hyper-threading
        1. Sockets, cores, and SQL Server licensing
        2. Making use of excess CPU capacity: data and backup compression
      4. Hyper-threading
    3. Processor Makes and Models
      1. Intel Xeon processors
        1. Intel Tick-Tock release strategy
        2. Future Intel Xeon releases
        3. Current recommended Intel Xeon processors
          1. One-socket server (OLTP)
          2. One-socket server (DW/DSS)
          3. Two-socket server (OLTP)
          4. Two-socket server (DW/DSS)
          5. Four-socket server (any workload type)
          6. Eight-socket server (any workload type)
      2. Intel Itanium and Itanium 2
      3. AMD Opteron processors
        1. Future Opteron AMD releases
        2. Current recommended AMD Opteron processors
          1. One-socket or budget two-socket server
          2. Two-socket server
          3. Four-socket server
    4. Server Motherboards: how to evaluate motherboards and chipsets
      1. Number of sockets
      2. Server chipsets
      3. BIOS
      4. Memory requirements
        1. Memory types
        2. Memory slots
        3. Memory architecture: SMP versus NUMA
      5. Network Interface Cards
    5. Choosing a Processor and Motherboard for Use with SQL Server
    6. Summary
  6. 2. The Storage Subsystem
    1. Disk I/O
    2. Drive Types
      1. Magnetic disk drives
      2. Solid-state drives
        1. Fusion-IO drives
        2. SSDs and SQL Server
    3. Internal Storage
    4. Attached Storage
      1. Direct Attached Storage
      2. Storage Area Network
    5. RAID Configurations
      1. RAID 0 (disk striping with no parity)
      2. RAID 1 (disk mirroring or duplexing)
      3. RAID 5 (striping with parity)
      4. RAID 10 and RAID 0+1
      5. RAID Controllers
    6. Provisioning and Configuring the Storage Subsystem
      1. Finding the read/write ratio
      2. How many disks?
      3. Configuration: SAN vs. DAS, RAID levels
    7. Summary
  7. 3. Benchmarking Tools
    1. Application Benchmarks
      1. TPC-C benchmark
      2. TPC-E benchmark
      3. TPC-H benchmark
      4. Analyzing benchmark test results
        1. TPC-E benchmark analysis sample
        2. TPC-E benchmark analysis by CPU type
    2. Component Benchmarks
      1. CPU and memory testing
        1. SPEC benchmarks
        2. Geekbench
          1. Verifying memory configuration
          2. Capacity and consolidation planning
      2. Disk I/O testing
        1. HDTune Pro benchmark
        2. CrystalDiskMark benchmark
      3. SQL Server-specific benchmarks and stress tools
        1. SQLStress
        2. SQLIO
          1. Set up: the param.txt file
          2. Running the tests: SQLIO command-line switches
          3. Interpreting storage test results
        3. SQLIOSim
    3. Summary
  8. 4. Hardware Discovery
    1. CPU-Z tool
    2. MSINFO32
    3. Windows Task Manager
    4. Computer Properties dialog
    5. SQL Server version information
    6. Summary
  9. 5. Operating System Selection and Configuration
    1. 32-bit or 64-bit?
      1. Advantages of 64-bit versions of Windows for SQL Server
      2. Disadvantages of 64-bit versions of Windows for SQL Server
    2. Windows Server: Versions and Editions
      1. Windows 2000 Server
      2. Windows Server 2003
        1. Performance and scalability enhancements
          1. HT and NUMA optimizations
          2. Kernel enhancements
        2. Editions and specifications
      3. Windows Server 2003 R2
      4. Windows Server 2008
        1. Performance and scalability enhancements
          1. Support for SMB 2.0
          2. CPU- and memory-related enhancements
          3. Kernel enhancements
          4. Hyper-V
        2. Editions and specifications
      5. Windows Server 2008 R2
        1. Editions and specifications
        2. Performance and scalability enhancements
          1. CPU- and memory-related enhancements
          2. Hyper-V enhancements
          3. SMB 2.1
        3. Editions and specifications
    3. Microsoft Support Policies for Windows Server
      1. Mainstream Support
      2. Extended Support
      3. Out-of-support case study
    4. Installing Windows Server and Service Packs
    5. Configuring Windows Server
      1. Windows Power Plans and CPU performance
        1. The hardware
        2. Tweaking the Windows, BIOS, and hardware settings
        3. Effect of Windows Power Plan setting on power usage and CPU performance
        4. Effect of Turbo Boost and hyper-threading
        5. Results summary
      2. Windows Instant File Initialization
      3. Lock pages in memory
    6. Summary
  10. 6. SQL Server Version and Edition Selection
    1. 32-bit or 64-bit SQL Server
    2. SQL Server Versions and Editions
      1. SQL Server 2005
      2. SQL Server 2008 Editions
        1. SQL Server 2008 Express Editions
        2. SQL Server 2008 Workgroup Edition
        3. SQL Server 2008 Web Edition
        4. SQL Server 2008 Standard Edition
        5. SQL Server 2008 Enterprise Edition
        6. SQL Server 2008 Evaluation Edition
        7. SQL Server 2008 Developer Edition
      3. SQL Server 2008 Enterprise Edition Features
        1. Performance and scalability features
          1. Data compression
          2. Enhanced Read Ahead and Advanced Scan
          3. Automatic use of indexed views
          4. Partitioning
          5. Distributed Partitioned Views
          6. Scalable shared databases
          7. Asynchronous database mirroring
        2. High Availability features
          1. Online index operations
          2. Parallel index operations
          3. Fast recovery
          4. Database snapshots
          5. Hot-add CPU and RAM
          6. Online restore
        3. Manageability features
          1. Resource Governor
          2. SQL Server Audit
          3. Change Data Capture
          4. Backup compression
          5. Mirrored backups
          6. Transparent Data Encryption
          7. More fail-over clustering nodes
          8. More instances
          9. Oracle Replication Publishing
          10. Peer-to-peer replication
      4. SQL Server 2008 R2
        1. SQL Server 2008 R2 Express Edition
        2. SQL Server 2008 R2 Web Edition
        3. SQL Server 2008 R2 Standard Edition
        4. SQL Server 2008 R2 Enterprise Edition
        5. SQL Server 2008 R2 Datacenter Edition
        6. SQL Server 2008 R2 Parallel Data Warehouse
        7. SQL Server 2008 R2 new features
          1. PowerPivot for SharePoint
          2. PowerPivot for Excel
          3. Utility Control Point
          4. Data Tier Applications
          5. Master Data Services
          6. Extended Protection
          7. Connectivity to SQL Azure
        8. SQL Server 2008 R2 improved features
          1. Support for 256 logical processors
          2. Unicode data compression
          3. Native backup compression
    3. Summary
  11. 7. SQL Server Installation and Configuration
    1. Preparation for SQL Server Installation
    2. Pre-Installation Checklist for SQL Server
      1. BIOS, firmware, and drivers
      2. Windows OS
      3. SQL Server components
      4. Network
      5. Accounts and privileges
      6. Logical drives and directories
      7. Functional and performance testing
      8. Fail-over clustering
      9. Installation media and Service Packs
    3. SQL Server 2008 R2 Installation
      1. SQL Server 2008 R2 Service Packs and Cumulative Updates
        1. How often to patch?
        2. Downloading and installing CUs and SPs
      2. SQL Server 2008 R2 Slipstream installation
    4. SQL Server 2008 R2 Instance Configuration Settings
        1. max server memory
        2. Optimize for ad hoc workloads
        3. Default backup compression
        4. Max degree of parallelism
        5. Number of TempDB data files
    5. Summary
  12. A. Intel and AMD Processors and Chipsets
    1. Processors
      1. Intel Xeon 3000 sequence
      2. Intel Xeon E3 sequence
      3. Intel Xeon 5000 sequence
      4. Intel Xeon 6000 sequence
      5. Intel Xeon 7000 sequence
      6. Intel Itanium 9000 series
      7. Intel Itanium 9100 series
      8. Intel Itanium 9300 series
      9. AMD Opteron 1200 series
      10. AMD Opteron 2200 series
      11. AMD Opteron 1300 series
      12. AMD Opteron 2300 series
      13. AMD Opteron 2400 series
      14. AMD Opteron 8200 series
      15. AMD Opteron 8300 series
      16. AMD Opteron 8400 series
      17. AMD Opteron 4100 series
      18. AMD Opteron 6100 series
    2. Chipsets
      1. Intel 3000, 3010, 3200, 3210, 3400, 3420
      2. Intel 5000P, 5000V, 5500, and 5520 chipsets
      3. Intel 7300 and 7500 chipsets
  13. B. Installing a SQL Server 2008 R2 Cumulative Update
      1. Obtaining a SQL Server 2008 R2 Cumulative Update
      2. Installing a SQL Server 2008 R2 Cumulative Update
  14. C. Abbreviations