1 / 27

Module 2 Preparing Systems for SQL Server 2008 R2

Module 2 Preparing Systems for SQL Server 2008 R2. Module Overview. Overview of SQL Server Architecture Planning Server Resource Requirements Pre-installation Testing for SQL Server. Lesson 1: Overview of SQL Server Architecture. SQL Server Architecture CPU Usage by SQL Server

danyl
Download Presentation

Module 2 Preparing Systems for SQL Server 2008 R2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 2 Preparing Systems for SQL Server 2008 R2

  2. Module Overview • Overview of SQL Server Architecture • Planning Server Resource Requirements • Pre-installation Testing for SQL Server

  3. Lesson 1: Overview of SQL Server Architecture • SQL Server Architecture • CPU Usage by SQL Server • Parallelism • 32 bit vs. 64 bit Servers • Overview of SQL Server Memory • Physical vs. Logical I/O • Demonstration 1A: CPU and Memory Configurations in SSMS

  4. SQL Server Architecture Users • Query Execution Layer • Parses and optimizes the queries, and manages the caching and execution of query execution plans • Storage Engine Layer • Manages buffer pages, I/O to the physical files, locking, and transactions • SQL OS Layer • Provides an abstraction layer over common OS functions, providing task and memory management SQL OS Query Execution Storage Engine Configuration

  5. CPU Usage by SQL Server • Windows uses preemptive scheduling of threads • One scheduler for every logical CPU created in SQL OS • Manages the threads retrieved from Windows and assigns tasks to threads • Minimizes context switches through cooperative scheduling • CPU availability can be configured without restart • Schedulers can be enabled or disabled • CPU affinity mask can be set • Tasks waiting on a resource are moved to a waiting list • Wait type and time are recorded • Details are useful for monitoring and troubleshooting

  6. Parallelism Parallelism refers to multiple processors cooperating to execute a single query at the same time. • SQL Server can decide to distribute queries to more than one task • Tasks can run in parallel • Overall execution is faster • Synchronization overhead is incurred • Parallelism is only considered for expensive plans • Max degree of parallelism defines how many CPUs can be used for execution of a parallel query • Can be overridden using the MAXDOP query hint • Cost threshold for parallelism defines minimal cost for considering parallel plans

  7. 32 bit vs. 64 bit Servers • Virtual Address Space is the memory that can be allocated to applications such as SQL Server • 4GB on 32 bit systems (2-3GB available for the application) • 8TB on X64 systems and 7TB on IA64 systems • 4GB for 32 bit applications running on WOW on 64bit OS • AWE extension can be used to access additional memory on 32bit systems • Additional memory can only be used for data page caching • SQL Server performance strongly depends on memory • Installing 64 bit versions is preferred • 64 bit options available for all editions of SQL Server

  8. Overview of SQL Server Memory • Buffer Pool is the main memory object of SQL Server • Holds data cache • Provides memory for other SQL Server components • Is divided into 8K pages • Components can request memory from VAS directly • Occurs when more than 8KB of continuous memory is required • SQL OS automatically allocates as much memory as needed • Has a mechanism to prevent memory shortage on the system • Can be configured using min and max server memory options

  9. Physical vs. Logical I/O

  10. Demonstration 1A: CPU and Memory Configurations in SSMS • In this demonstration, you will see: • How to configure SQL Server CPU and Memory using SSMS • How to review configuration options using T-SQL

  11. Lesson 2: Planning Server Resource Requirements • Introduction to Planning Server Resource Requirements • Discussion: Previous Exposure to Resource Planning • Planning CPU Requirements • Planning Memory Requirements • Planning Network Requirements • Planning Storage and I/O Requirements

  12. Introduction to Planning Server Resource Requirements • No easy formula for calculating resource requirements exists • Planning involves • Checking with ISV or developers • Ask for reference installations and case studies • Request details of previous experiences with the application • Performing tests with real live workloads • Setting goals and evaluating results against them • Planning and predicting further grow of the workload and database • Monitor the outcome after release into production

  13. Discussion: Previous Exposure to Resource Planning • Resource Planning is an important part of new installations • What is your previous experience with planning of new systems? • How successful was the planning?

  14. Planning CPU Requirements • Processor planning is relatively straightforward • Test on a typical workload and monitor CPU usage • CPU should be below 30 percent in average • Peaks can occur but should not last too long • More processors provide better parallelism options • Assists with large numbers of concurrent connections • Assists with high data warehouse loads • Dedicated database server should use all available CPUs • Many new systems use NUMA architecture • Check with hardware vendor for optimal SQL Server configuration

  15. Planning Memory Requirements • SQL Server is highly dependent on memory • Caches help to reduce I/O and CPU • Data cache reduces I/O • Plan cache reduces the need for recompiling queries • Memory to store and process data is required during query execution • Memory shortage can lead to CPU and I/O pressure • Plan to have enough memory • Able to cache frequently accessed data • Consider memory consumption of other services running on the system

  16. Planning Network Requirements • Plan and test the throughput needed for the application • Consider using several network adapters • Consider dedicating a network connection to administrative purposes • Writing Backups to network devices • Transferring data to other instances using SSIS • Use a dedicated network connection when using database mirroring • Choose and configure the network protocols used • Check firewall configurations • Test in a network environment similar to the target production system

  17. Planning Storage and I/O Requirements • I/O requirements need to be determined and tested • Considerations for storage • Dedicated vs. SAN based storage • RAID systems used • Number of spindles involved (often more important than disk size) • I/O caching configuration (disk and write caching)

  18. Lesson 3: Pre-installation Testing for SQL Server • Overview of Pre-installation Testing • Perform Pre-checks of I/O subsystems • Introducing SQLIOSIM • Introducing SQLIO • Demonstration 3A: Using SQLIOSIM & SQLIO

  19. Overview of Pre-installation Testing • Planning and testing is strongly coupled • After the first planning phase tests must be performed • Check if the predictions made are met • Change the predictions based on the results • Check if the goals set in the planning phase are fulfilled • Test different configurations involving • Storage and I/O layout • Memory, CPU and Network configurations • Document every test run for later use

  20. Perform Pre-checks of I/O subsystems • Test different I/O types and sizes • Small random reads and write on data files for OLTP systems • Larger sequential reads and writes for OLAP systems • Sequential writes for log storage • Test with different numbers of files involved • Use test files that are similar to your target configuration • Determine the saturation point of an I/O subsystem by gradually increasing the load • Validate results against expected outcome • Based on the goals you set • Checked against the potential I/O capabilities of the system

  21. Introducing SQLIOSIM • Unsupported tool provided by Microsoft to test the functional correctness of storage systems. • Can be run through command line and and GUI

  22. Introducing SQLIO • Unsupported tool provided by Microsoft to test the performance of storage systems. • Command line tool that can be configured to specific I/O Pattern

  23. Demonstration 3A: Using SQLIOSIM & SQLIO • In this demonstration, you will see how to: • Configure SQLIOSim to stress test a server • Test different I/O patterns using the SQLIO utility

  24. Lab 2: Preparing Systems for SQL Server • Exercise 1: Adjust memory configuration • Exercise 2: Perform Pre-installation Stress Testing • Challenge Exercise 3: Check Specific I/O Operations (Only if time permits) Logon information Estimated time: 45minutes

  25. Lab Scenario You have reviewed the additional instance of SQL Server. A system administrator at AdventureWorks has expressed some concerns that the existing server may not have enough memory or I/O capacity to support this new SQL Server instance and is reviewing a new I/O subsystem. As the database administrator, you need to review the available server memory and the memory allocated to each of the existing SQL Server instances. You need to ensure that the I/O subsystem of the new server is capable of running SQL Server and the required workload correctly.

  26. Lab Review • Why is running SQLIOSim on the VM difficult? • Should you use SQLIOSIM or SQLIO to test the performance of a system with 8KB random reads?

  27. Module Review and Takeaways • Review Questions • Best Practices

More Related