SQL Server database tuning and optimisation skills

The course is designed for database experts (operators, developers) with previous SQL Server basics and query knowledge and experience, who will be responsible for optimizing SQL Server 2016 databases, queries and implementing execution plans, and who want to gain the deeper technological knowledge required.

• Overview of SQL Server architecture: SQL Server components, functions and their roles; overview of performance factors; the concept of Scheduling, comparison of Windows Scheduling and SQL Scheduling; the concept of Wait / Queues.
• SQL Server I/O operation: concepts, storage solutions, I/O configuration and testing, measuring storage performance.
• Database structures: structure, operation; structure and operation of datasets; TempDB concept and operation.
• Memory management: Windows memory management, SQL Server memory management, in-Memory OLTP concepts and operation, memory usage testing.
• Concurrency and transaction management: concepts, how locking works, how concurrency and transactions work and are managed.
• Statistics and index management: concepts (statistics, Cardinality Estimation), how indexing works, how columnstore indexes work and how to use them.
• Execution plans: query execution and optimisation concepts, analysis of query plans.
• Cache and recompilation planning: concepts, cache strategy planning, cache failure detection, query store concepts and usage.
• Extended events: concepts, operation, implementing extended events.
• Performance monitoring: monitoring and tracking tools, setting baselines for SQL performance data, benchmarking.
• Detecting and troubleshooting general performance errors: CPU performance errors, memory errors, I/O errors, concurrency issues, TemDB performance errors.

Windows Server 2012 or 2016 operator basics, previous SQL Server basics, query and implementation knowledge and experience. Completion of or knowledge of 20461 or 20761 is required. Completion of or knowledge of 10776 (20464) or 20762 is recommended. As the course materials are in English, a basic knowledge of English at document reading level is required. The lectures will be given in Hungarian.