Capacitas Logo
About Us
Our Solutions
Our Services
Industries
Our Clients
Investor Relations
Training
Portfolio
Accreditation
Training Calendar
Onsite Training Quotation
Feedback
Research
News & Events

Capacity Planning & Performance Management of Microsoft SQL Server

Capacitas Training Centre,
London, United Kingdom.
2 Days

Price: £1,280 +VAT

Scheduled Dates:
26th to 27th Mar 2012

Learn how to:

  • Collect and correctly interpret performance measures from SQL Server systems
  • Use 'rules of thumb' to assess the capacity requirements of new SQL Server based applications
  • Capacity and performance manage the processor, memory, disk and network subsystems of SQL Server
  • Extract configuration information from SQL Server
  • Characterise workloads on SQL Server systems
  • Employ established sizing techniques for your application environments
  • Diagnose SQL server performance problems

Course Agenda

Day 1
9:00 - 9:30 Registration
09:30 - 10:00 Introduction
  • SQL Server overview
  • Microsoft SQL Server Architecture
  • SQL Server 2005
  • SQL Server roadmap
  • SQL Server 2008
  • Capacity constraints with specific SQL Server editions
10:00 - 11:00 Demand Planning SQL Server
  • Understanding ITIL business layer demand
  • Understanding ITIL service layer demand
  • Understanding ITIL component layer demand
  • Physical vs. logical capacity in SQL Server
11:00 - 11:15 Morning Break
11:15 - 13:00 Demand Planning SQL Server
  • Case Study: Demand Planning SQL Server
13:00 - 13:45 Lunch
13:45 - 15:00 Performance Monitoring SQL Server
  • Using Sysmon/Performance Monitor
  • Using SQL Profiler
  • Case Study: Using SQL Profiler
  • SQL Server Management Studio
  • I/O monitoring with the fn_virtualfilestats tool
  • Performance monitoring with dynamic management views (DMVs)
  • Performance monitoring with PSSDIAG
  • Recommended performance metrics to monitor
  • Monitoring latches and locks
  • Component, service and resource layer performance monitoring
  • Exercise: Analysing SQL Server Performance
15:00 - 15:15 Afternoon Break
15:15 - 17:30 Understanding SQL Server Workload
  • SQL Server workload characterisation
  • Analysing batch and online workloads
  • Identifying key drivers of SQL server workload
  • Workload characterisation with SQL Profiler
  • Workload characterisation with dynamic management views
  • Example: SQL Server workload characterisation
 
Day 2
9:00 - 11:00 Processor Capacity and Performance Management
  • Understanding SQL Server Processor configuration
  • SQL Server Processor capacity planning
  • SQL Server Processor performance
  • Predicting processor capacity requirements for SQL Server systems
  • Applying industry benchmarks to sizing exercises
  • SQL parallelism
  • Optimizing SQL Server stored procedures
  • Database Tuning Advisor
  • Case Study: Capacity Planning SQL Server
11:00 - 11:15 Morning Break
11:15 - 13:00 Disk Capacity and Performance Management
  • Understanding SQL Server disk configuration
  • SQL Server disk space capacity planning
  • File space capacity planning
  • SQL Server I/O performance
  • Understanding asynchronous nature of SQL Server I/O
  • Case Study: Analysing SQL Server I/O performance
  • Identifying capacity bottlenecks
  • Modelling the I/O subsystem
  • Rules of thumb for sizing new services
13:00 - 13:45 Lunch
13:45 - 15:45 Memory Capacity and Performance Management
  • SQL Server memory configuration
  • Memory constraints arising from OS version or SQL Server edition
  • SQL Server memory workload in the context of Windows Server memory management
  • SQL Server buffer manager
  • Buffer cache tuning
  • Case Study: Analysing SQL Server memory
  • Sizing new database services
  • Identifying capacity bottlenecks
15:45 - 16:00 Afternoon Break
16:00 - 16:30 Network Capacity and Performance Management
  • SQL Server network performance
  • SQL Server network capacity planning
  • Identifying capacity bottlenecks
16:30 - 17:30 SQL Server Resource Governor
  • Resource management with Resource Governor
  • Limitations of Resource Governor
  • Resource Pools
  • Workload Groups
  • Using Resource Governor

Course Brief

SQL Server based applications are widely deployed in today's ICT environments, often supporting business-critical services. This course teaches the capacity planning and performance tuning techniques specific to Microsoft SQL Server. It provides the insight required to ensure your SQL Server-based applications meet required levels of service whilst minimising cost.

The course now contains numerous 'consultant's tips' that can be applied rapidly within your organisation. Exercises are used to reinforce key learning. The information in this course is applicable to SQL Server versions 2000, 2005 and 2008. The application of techniques is demonstrated with real-life case studies.

Course Prerequisites

Course Audience

  • Capacity Planners
  • Performance Analysts
  • System Administrators
  • ICT Managers
  • System Testers
  • Operations Staff

How Your Organisation Will Benefit

Attending this course you will learn:
  • How to avoid costly hardware upgrades through effective capacity planning and performance tuning
  • Effective and pragmatic capacity planning techniques for SQL Server applications
  • How to characterise the workload in complex database environments
  • How to reduce operational costs through performance management
  • What performance metrics should be collected as part of a SQL Server performance monitoring strategy
  • How to rapidly assess the capacity and performance of SQL Server applications
  • How to leverage bundled tools to deliver effective capacity and performance monitoring of SQL Server
  • To ensure service level compliance through capacity and performance monitoring
  • How to conduct ITIL-based demand planning of SQL Server applications

Or consider an onsite event.
© Capacitas Ltd 2012 Privacy Policy Code of Professional Practice