Course Outline
MODULE 1 : Installing SQL Server , Baseline , Hardware sizing
In this module we cover aspects of SQL Server Database administration such as Installing SQL Server and configuring it to best utilize the hardware, we also cover base line of the hardware and implementing best practices such as TempDB optimization, PBM, RAID Configurations and Disk and Memory Sizing for database servers. We will also cover aspects such as different architectures followed when designing a database. Using SQLIOSIM etc will also be covered in this module.
MODULE 2 : Performance Monitoring Tools
In this module we explore the different tools available to monitor performance of the sql server instance.
SQL Server Activity Monitor, PerfMon, Data Collectors, RML, PAL, Profiler, DMVs, Extended Events, DRC
MODULE 3 : SQL Internals
In this module we cover a quick introduction to SQL Server internals and the main components and behaviours of SQL OS with regard to IO, CPU and MEMORY, we explain the roles played by the execution engine and the storage engine as well as the way they affect performance based on recovery model, isolation levels and server settings.
MODULE 4 : Fixing CPU issues
In this module we cover common CPU issues faced by DBAs and Developers when working with SQL Server, we explore how to identify the correct root cause using the tools mentioned in module 2 and explore how to fix the root cause. Some key topics that will be covered in this module include.
Parallelism, Hyper Threading and Multi Core, NUMA, Wait and Queues, Scheduler, Thread Counts, indexes, Resource Governor, Server Side trace etc.
MODULE 5 : Fixing Memory Issues
In this Module we cover memory architecture of SQL server, covering in detail the processes and way memory is allocated and de-allocated, we explore the buffer pool as well as the difference between 32 and 64 bit architecture. We work with DMVs used to identify memory pressure and performance counter used to measure memory utilization. We explore the use of indexes in improving memory utilization and some database design tips and tricks that can improve memory footprint such as filtered indexes, compression, partitioning, in memory structures, columnstore indexes, rewriting the query etc.
MODULE 6 : Fixing IO Issues
In this module we cover the most common root cause of SQL Server performance issues, the Disk IO. We explore files and file groups , the internals of mdf and ldf file management , VLFs , Auto growth and Shrink , impact of TDE on disk IO, Partitioning, Storage Engine Internals , Locking and Blocking, Latches , Trouble shooting tempdb bottlenecks, improving write throughput , performance counters to measure disk latency and more.
MODULE 7 : Locks and Latches
In this module we explore the execution engine and learn how to understand the execution plan we cover locking, blocking and deadlocks. In addition we explore isolation levels and recovery models impacts on data durability, consistency and performance. We will also explore row versioning, snapshots and locking hints.
MODULE 8 : Indexing
In this module we cover internals of indexes, B-Trees , how to identify the right index for the job, how to implement indexed views, reusing indexes, working with statistics, the different types of indexes can when they are most useful, fragmentation in indexes , Scalar UDF , CLR assemblies , Plan Guides and Hints, Joins and Join Algorithms.
MODULE 9 : Everything else
In this module we cover some other not so common ways to approach performance issues, we explore how developers and DBA can achieve performance benefits by using hints , rewriting the query , identify problem queries , Parameter sniffing , SQL plan recompile , dynamic queries , VAS , other common performance issues , QnA .