Module 2

Preparing Systems for

SQL Server 2012
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

• 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
SQL Server Architecture

Users • Query Execution Layer

• Parses and optimizes the
queries, and manages the
caching and execution of query
execution plans

• Sto
Storage
age Engine
g e Layer
aye
Query Execution • Manages buffer pages, I/O to
the physical files, locking, and
transactions

• SQL OS Layer

Storage
g Engine
g • Provides an abstraction layer
over common OS functions,
f ti
providing task and memory
management
SQL OS
CPU Usage by SQL Server

• Windows uses pre-emptive 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
g on a resource are moved to a waiting
g list
• Wait type and time are recorded
• Details are useful for monitoring and troubleshooting
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
g of p
parallelism defines how many
y CPUs can
be used for execution of a parallel query
• Can be overridden using the MAXDOP query hint

• Cost
C t threshold
th h ld for
f parallelism
ll li d fi
defines minimal
i i l costt ffor
considering parallel plans
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
y (2-3GB
( available for the application)
pp )
• 4GB for 32 bit applications running on WOW on 64bit OS
• 8TB for 64 bit systems

• AWE extension can no longer be used to access additional

memory on 32bit systems
• Could be a significant issue for 32 bit upgrades to SQL Server 2012

• Itanium processors are no longer supported

• SQL Server performance strongly depends on memory

• Installing 64 bit versions is preferred
• 64 bit options
p available for all editions of SQL
Q Server
Overview of SQL Server Memory
• Buffer Pool is the main memory object of SQL Server
• Holds data cache
• P
Provides
id memory ffor other
th SQL Server
S components
t
• Is divided into 8K pages

• SQL OS automatically allocates as much memory as needed

• Has a mechanism to prevent memory shortage on the system
• Can be configured
g using
g min and max server memory
y options
p
Physical vs. Logical I/O

I/O Type Description

Physical I/O occurs when the requested page

is not available in Buffer Cache and must be
read from the data file into the Buffer Cache
Physical I/O
before the requested page can be supplied or
when a changed page is written to the data
file

Logical I/O occurs when the requested page

Logical I/O
is available in the Buffer Cache
Demonstration 1A: CPU and Memory Configurations
in SSMS
In this demonstration, you will see:
• How to configure SQL Server CPU and Memory using SSMS
• H
How tto review
i configuration
fi ti options
ti using
i T-SQL
T SQL
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

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
gggoals and evaluating
g results against
g them
• Planning and predicting further growth of the workload and
database

• Monitor
M it th the outcome
t after
ft release
l into
i t production
d ti
Discussion: Previous Exposure to Resource Planning
• Resource planning is an important part of new
installations
• What is y
your previous
p experience
p with planning
p g of new
systems?
• How successful was the planning?
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
Planning Memory Requirements
• SQL Server is highly dependent on memory
• Caches help to reduce I/O and CPU
• D t cache
Data h reduces
d 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
C id memory consumption
ti off other
th services
i running
i on
the system
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
g Backups
p 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
• Ch k fi
Check firewall
ll configurations
fi ti
• Test in a network environment similar to the target production system
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)
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

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
• Ch k if the
Check th goals
l sett in
i the
th planning
l i phase
h are fulfilled
f lfill d

• Test different configurations involving

• Storage and I/O layout
• Memory, CPU and Network configurations

• Document every test run for later use

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
T t with
ith different
diff t numbers
b off files
fil involved
i l d
• 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
Introducing SQLIOSIM

• Unsupported tool provided by Microsoft to test the functional

correctness of storage systems
• Can be run through command line and GUI
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
Demonstration 3A: Using SQLIOSIM & SQLIO
In this demonstration, you will see how to:
• Configure SQLIOSim to stress test a server
• T t diff
Test differentt I/O patterns
tt using
i the
th SQLIO utility
tilit
Lab 2: Preparing Systems for SQL Server
• Exercise 1: Adjust Memory Configuration

• Exercise 2: Pre-installation Stress Testing

• Challenge Exercise 3: Check Specific I/O Operations (Only

if time permits)

Logon information

Virtual machine 10775A-MIA-SQL1

User name AdventureWorks\Administrator

d k \ d
Password Pa$$w0rd

Estimated time: 45 minutes

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.
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?
Module Review and Takeaways
• Review Questions

• Best Practices