SQL Server - SQL Query Tuning


Course Facts

  • 3 day course
  • Our Trainers are Authorised Professionals
  • Live Online or Classroom-based
  • Certificate of completion
  • Maximum class size of 6 for scheduled courses
  • Unlimited support after your course
  • Courses at all levels: Introduction, Intermediate, Advanced and Masterclasses

Course Outline

  • Performance and Tuning
  • Monitoring Performance
  • Memory Management
  • Process and Thread Management
  • I/O Management
FULL OUTLINE

Private One-to-One Training

One-to-one tuition tailored to your exact requirements. Your choice of duration, dates and content.

Enquire Now

Team Training for Companies

Group bookings provide you with the most cost-effective way to train your team.

Enquire Now

Team Training and Bespoke Sessions

Booking a private training session allows you to focus on your exact needs on dates that work best for you or your team.

  • Sessions can be run online or in person, on your site or at our London training centre.
  • We manage training for many of the UK’s largest companies, taking care of all the admin, working with you to ensure the perfect course.
  • Our expert trainers will first understand your requirements and then tailor each session to your exact specification.
  • We offer a free consultation service to work out every detail of your course.

Prices for bespoke sessions with our expert trainers start at £525 + VAT.

Overview

The objective of this course is to provide senior database analysts and developers with a good understanding of SQL Server Architecture and Query Performance so that applications and their underlying queries, indexing and locking strategies can be tuned for optimal performance. The course will provide delegates with the knowledge required to 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 architecture through to query and index monitoring & tuning 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 SQL Profiler, Extended Events, Execution Plans 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.
Read More...

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

Our Clients

Our Recent Success Stories

eLearning Training

eLearning

Julie's Story

Learning Technologist at Imperial College

Animate Training

Animate

Stuart's Story

Graphic Designer at S4C

Consultancy and Development Training

Consultancy and Development

Carly's Story

Managing Director at FSL Aerospace

30 years working with Microsoft
Five Star Training Five Star Training Five Star Training Five Star Training Five Star Training   600+ Reviews

Why Transmedia?

Unlimited Training Support

We offer unlimited training support after your course.

Bespoke Training Solutions

Customised training and software solutions based on your exact specification.

Worldwide Creative Skills Training

Join our authorised training courses live online from anywhere in the world.

Authorised Training Professionals

Our experts are accredited by Adobe, Apple and Claris and bring years of experience and real-world expertise.

Courses Live Online

Join our virtual classrooms to learn from the comfort of your own space in real time from our accredited training experts.

SEE COURSES

Classroom Training

Join us at one of our Authorised Training Centres for a traditional classroom-based experience.

We have everything ready for you to have the best training experience.

SEE COURSES

Private Courses

One-to-one tuition or a private group training with just you or your colleagues.

We can train you live online, at one of our centres or onsite, wherever you are located.

SEE COURSES

Latest Help Station Articles