SQL Server Performance and Tuning for DBA

Duration: 3 Days

Objectives

The objective of this course is to provide senior Database Administrators (DBAs) with a good understanding of SQL Server Architecture and Query Performance so that SQL Server architecture, hardware and instances can be tuned for optimal performance. The course will provide delegates with the knowledge required to size, plan, monitor, tune and troubleshoot a SQL Server application for either a OLTP live environment or a OLAP data warehousing environment. Everything from Operating System & SQL Server service tuning through to an over view of and index and locking strategies will be covered. It is a hands on course with performance and analysis tools being put to use throughout. Delegates will learn how to capture and analyse performance data to enable troubleshooting of performance problems using tools such as Performance Monitor, SQL Profiler, Extended Events and Dynamic Management Views. They will learn how to interpret query execution plans to determine where the cause of bottlenecks lies and how to tune the queries for best performance. Covers all versions from SQL Server 2008 through to SQL Server 2017.

Pre-requisites

Delegates should have database administration experience with SQL Server 2005 or later, and should have a querying knowledge level equivalent to that provided by our SQL Server Database Querying Course.

Course includes

A comprehensive set of reference notes covering course topics, follow-up telephone support and, for training on Transmedia premises, lunch and refreshments.

Course outline

Performance and Tuning

  • Sizing and Planning
  • Planning
  • Sizing
  • Sizing and Planning Considerations

Monitoring Performance

  • Analysing Performance Data
  • Tuning and Optimising Performance
  • Application Tuning
  • Database Tuning: Normalised, De-Normalised, Indexing, Constraints
  • SQL Server Tuning
  • Hardware Tuning
  • Operating System Tuning
  • Useful Tuning Tips

Memory Management

  • Operating System Memory Limitations
  • Virtual Memory Model: Physical RAM, Pagefile, Page Faults, Paging
  • Reserved and Committed Memory
  • Process Address Space
  • 32-bit and 64-bit Platforms
  • SQL Server and Memory Management
  • Memory Pool
  • The Buffer Cache
  • Shared Memory
  • Viewing Memory Performance: Task Manager, Performance Monitor, Pviewer

Process and Thread Management

  • Windows Thread Scheduling
  • Quantum Units and Clock Interval
  • Thread States and Thread Priorities
  • Process Priority Levels and Thread Priority Levels
  • Foreground V Background Processes
  • The Processor Queue
  • SQL Server and Threads and Processes
  • SQLOS and Thread Scheduling
  • Worker Thread Pooling
  • SQL Server and CPU Limits
  • Monitoring Processes and Threads: Task Manager, Performance Monitor, Pviewer

I/O Management

  • Windows I/O
  • Synchronous I/O and Asynchronous I/O
  • Buffered I/O and Nonbuffered I/O
  • SQL Server and I/O
  • Disk Drives and Disk Geometry
  • Rotational Latency and Disk Seek Time
  • Sequential I/O and Random I/O
  • SQL Server Transaction Log File
  • RAID: RAID 0, RAID 1, RAID 5, RAID 10
  • RAID Performance
  • Solid State Storage (SSD)
  • SQL Server and RAID: Operating System, Transaction Log, Data Files, TempDB
  • Buffer Pool Extension
  • SQL Server and Disk Performance
  • Monitoring Disk Performance: Performance Monitor

Networking

  • Named Pipes
  • Shared Memory
  • TCP/IP (Sockets)
  • Encryption
  • Network Performance
  • SQL Server Architecture
  • The Network Library
  • The Database Engine
  • Relational Engine: Parser, Optimiser, Compiler, Open Data Services (ODS), The SQL Manager, Expression Manager, The Query Executor
  • The Storage Engine: Transaction Manager, Page Manager andText Manager, Access Methods Manager, Lock Manager, File/Device Manager, Buffer Manager, Log Manager, Sort Manager
  • Data Storage: Data Files, Transaction Log File, Filegroups
  • Units Of Storage: Pages, Mixed Extents and Uniform Extents
  • Data Page Types: Data Page, Index Page, Text/Image Page
  • Internal Page Types: Page Free Space (PFS) Page, Global Allocation Map (GAM), Shared Global Allocation (SGAM) Page, Index Allocation Map (IAM) Page, Bulk Changed Map (BCM) Page, Differential Changed Map (DCM) Page
  • Data File Page Structure: Locating Data, Table Scans, Index Seeks
  • Transaction Logging: Architecture, Virtual Logs, Truncation, Checkpoints, Recovery Interval
  • Viewing Index and Data Pages: DBCC IND, DBCC PAGE, sys,indexes, sys.partitions, sys.system_internals_allocation_units
  • Viewing Transaction Log Content: fn_dblog, fn_dblog_dump, DBCC LOGINFO

SQL Server and Virtualisation

  • Key Virtualisation Considerations: Performance, Complexity, Vendor Support
  • Processor Best Practices: Physical to Virtual CPU ratios
  • Virtual memory Best Practices: Hot-add RAM, Lock Pages In Memory
  • Storage Best Practices: Virtual Hard Disks (Fixed, Dynamic, Differencing) or Raw Disks (pass-through)
  • Tiered Storage Solutions: Flash (SSD) and SATA
  • Virtualization and Availability

High Availability and Disaster Recovery

  • HA and DR Methods: Log Shipping, Database Mirroring, Clusters, AlwaysON Availability Groups, Replication
  • Performance Considerations: Latency, Load, Like for Like

Planning For A SQL Server Database Application

  • Capacity Planning
  • Processors
  • Memory
  • Disk
  • Network

System Performance and Tuning

  • What Is Performance?
  • Bottlenecks
  • Measuring Performance
  • What Is Tuning?
  • Fix The Right Bottleneck
  • Processor Bottleneck
  • I/O Bottleneck
  • Memory Bottleneck
  • sp_configure
  • SQL Server CPU Tuning
  • SQL Server Memory Tuning
  • IO Tuning

Performance Monitor

  • Performance Objects, Counters and Instances
  • Viewing System Activity: Chart View, Report View, Histogram View
  • Performance Logs and Alerts: Data Collections
  • Counter Types: Instantaneous, Averaging, Delta
  • Processor or System Counters: Processor Queue Length and Usage, Context Switches, etc.
  • Memory Usage Counters: Physical Memory, Virtual Memory, Paging, etc.
  • Disk Counters: Disk Queue Length and Usage, Disk Reads, Disk Writes, etc.
  • SQL Server Performance Objects and Counters: File Sizes, Log Usage, Buffer Cache, Procedure Cache, Locks and Latches, Recompilations, etc.
  • Network Counters

SQL Profiler

  • Trace Events: Event Classes, Events, Data Columns, Filters
  • Saving Trace Data
  • Retrieving Trace Data From A Trace Table
  • Retrieving Trace Data From A Trace File
  • Server Side Tracing: sp_trace_create, sp_trace_setevent, sp_set_filter, sp_trace_setstatus
  • Analysing Trace Data
  • CPU Usage and Long Running Statements
  • Heavy I/O Users
  • Detecting Deadlocks
  • Inefficient Stored Procedure Caching
  • Transaction Performance
  • Auditing With Traces
  • Coalescing Performance Logs With Profiler Trace Files
  • Profiler Replay

Extended Events

  • Introduction to Extended Events
  • Extended Events Sessions
  • Events: Event Fields
  • Actions
  • Filters
  • Targets: Event Files, Ring Buffer, Event Counter, Histogram, Pair Matching
  • Buffer Size
  • Event Retention
  • Creating Extended Events Sessions: TSQL, SSMS GUI
  • Viewing Captured Data
  • Troubleshooting blocking and deadlocking
  • Track long running queries
  • Identify Missing Statistics
  • Identify Long Running IO Operations
  • Identify Memory Pressures

SSMS Reports

  • Standard Reports
  • Custom Reports
  • Performance Dashboard

DBCC

  • DBCC SQLPERF
  • DBCC SHOW_STATISTICS
  • DBCC SHOWCONTIG
  • DBCC PROCCACHE
  • DBCC MEMORYSTATUS
  • DBCC IND
  • DBCC PAGE
  • DBCC LOGINFO

The Data Collector

  • Configuring The Data Collection Service
  • Configure Management Data Warehouse
  • Configure Data Collection
  • Viewing The Collected Data
  • Log Viewer
  • Data Collection Properties for Query Statistics
  • Data Collection Properties for Server Activity
  • Data Collection Properties for Disk Usage
  • Data Collection and Upload: Manually and Scheduled
  • Queries on msdw Database
  • View Data Collection Reports

Dynamic Management Views and Functions

  • SQLOS Views: sys.dm_os_
  • Waiting Stat Views: e.g. sys.dm_os_wait_stats, sys.dm_os_waiting_tasks
  • Memory Views: e.g. sys.dm_os_memory, sys.dm_os_process_memory
  • SQL Server Instance Views: e.g. sys.dm_os_sys_info, sys.dm_os_tasks
  • Sessions Views: e.g. sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_requests
  • Query Plan Views: e.g. sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_exec_query_plan
  • IO Views: e.g. sys.dm_io_pending_io_requests
  • Locking views: e.g. sys.dm_tran_locks
  • Buffer Cache Views: e.g. sys.dm_os_buffer_descriptors
  • Index Views: e.g. sys.dm_db_index_operational_stats, sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats
  • Query Optimizer Views: sys.dm_exec_query_optimizer_info, sys.dm_exec_query_transformation_stats
  • And many more

Optimal Database Design

  • Database Design: OLTP Databases, OLAP Databases, Hybrid Database
  • Table Design
  • Table Constraints
  • Triggers and Stored Procedures
  • Client Side Programming

Indexing

  • Indexing Strategy: OLTP, OLAP, Hybrid
  • Index Architecture: B-Tree Index, Non-clustered B-Tree, Clustered B-Tree
  • UNIQUE and DUPLICATE Index
  • NONCLUSTERED and CLUSTERED Index
  • Covering Index
  • INCLUDE Index
  • Filtered Index
  • New features in 2012/2014
  • Columnstore Indexes
  • Index Optimisation
  • Creating Indexes
  • Index Views: sys.indexes , sys.partitions, sys.system_internals_allocation_units
  • Index Optimisation: Query Optimiser, Index Statistcs, Index Selectivity
  • Index Fragmentation: Index Depth and Density, DBCC SHOWCONTIG, sys.dm_db_index_physical_stats, Defragmenting/Rebuilding An Index
  • Index Growth: FILLFACTOR, PADINDEX
  • The Database Engine Tuning Advisor
  • Monitoring Index Usage
  • Displaying Information About Indexes
  • Dynamic Management Views and Functions for Indexes

Table Maintenance and Performance

  • Row Migration
  • Page fragmentation
  • Forward Fetches
  • Data Type Efficiency: varchar(max), nvarchar(max), varbinary(max)
  • In-Memory OLTP

Locking

  • Locking Granularity: Database Lock (DB), Heap or B-Tree (HOBT), Table Lock (TAB), Extent Lock (EXT), Page Lock (PAG), Row Level Lock (RID), Index-Key Lock (KEY), Key Range Lock, Application Lock (APP)
  • Locking Modes: Shared Lock (S), Exclusive Lock (X), Update Lock (U), Intent Lock (I), Schema Stability, Schema Modification, Bulk Update
  • Key Range Locks: RangeS-S, RangeS-U, RangeIn-Null, RangeX-X, RangeIn-S, RangeIn-U, RangeIn-X, RangeX-S, RangeX-U
  • Locking Compatibility
  • Transaction isolation Levels: READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT, READ COMMITTED SNAPSHOT
  • Deadlocks
  • Tools For Monitoring Locks: Activity Monitor, sp_lock, DMVs, Profiler, Extended Events, DBCC
  • Deadlock Graph

Query Optimisation

  • Compilation and Execution: Sequence Tree, Query Optimiser, Optimiser Rules, Simplification, Statistics Loading, Cardinality Estimation
  • Trivial Plan Optimisation
  • Cost-Based Optimisation
  • Evaluating Costs: Query Analysis (SARGS), Index Selection
  • Index Statistics: DBCC SHOW_STATISTICS, DMVs
  • Join Selection: Nested Loop Join, Merge Join, Hash Join
  • Updating Index Statistics
  • Column Statistics
  • The Query Plan
  • Procedure Cache
  • Query Hash and Query Plan Hash
  • Viewing Procedure Cache Content
  • Clearing The Procedure Cache

Analysing Queries

  • Query Execution Plan: Graphical, SHOWPLAN_TEXT, SHOWPLAN_ALL, SHOWPLAN_XML
  • Graphical Execution Plan: Operation Properties, The Properties Window, The Physical Operators
  • Interpreting SHOWPLAN Results
  • Viewing The Actual Query Plan
  • Logical and Physical Operators
  • Joins: Inner Joins, Outer Joins, Cross Joins, Subqueries
  • RID Lookups and Key Lookups
  • Compute Scalar
  • Correlated Sub Queries
  • Viewing IO Statistics: SET STATISTICS IO
  • Profiler Traces and Query Plans

Database Application Tuning

  • Tuning SQL Statements
  • Interactive Transactions
  • Query Timeouts
  • Cursors
  • Long Running Queries
  • Comparing Queries
  • SQL Hints
  • Join Hints
  • Table Hints
  • Query Hints
  • Using Stored Procedures and User Defined Functions
  • Autoparameterisation
  • Sp_executesql Stored Procedure
  • Procedure Cache
  • User-Defined Functions
  • Views
  • Partitioned Tables and Indexes
  • Maintaining Table and Index Fragmentation

Resource Governor

  • Resource Pools and Workload Groups
  • Resource Governor Properties
  • Classification Function
  • Creating A Resource Pool and Workload Group: SSMS and TSQL
  • Implement the Classification Function

Plan Guides

  • Creating a Plan Guide
  • Managing Plan Guides

Course Dates

Location: just outside London

The new course schedule is currently being updated and will be ready shortly. Please phone us on 0208 269 4260 or email us for the latest dates.

All our courses can be tailored to requirements at your own offices. For alternative dates, please email or call us on 0208 269 4260
All prices are per person and are subject to VAT
15% discount for groups of four delegates.
10% discount for groups of three delegates.
(This offer applies for delegates on the same public scheduled course on the same date.)

Related Courses


"Concepts were explained clearly - very much so. What a great instructor! "

Jason Mather

"An excellent presentation, very clearly explained and enjoyable at the same time. Has given me confidence in using this software."

RAF Culdrose

"Excellent. The right pace, the right style- great overall!"

Goldsmiths University of London



© Transmedia . 85 Tottenham Court Road, London, W1T 4TQ  UK . 0208 269 4260       Privacy Policy