SQL Server Optimization (Part 1)

The series of sessions will give you a wealth of practical optimization techniques that you can put into production as soon as you return to work and we ensure you understand *why* and *how* a technique works rather than just what the technique is. This usually means we will explain how SQL Server works internally while discussing a specific topic.

Who is the Audience?
Intended for SQL Server administrators who are responsible for the performance of their database servers and installation and also SQL Server developers who are responsible for developing SQL Server queries and stored procedures.

Primary responsibilities will include:

• Providing database administration tasks.
• Monitoring SQL Server instance and database performance.
• Investigate and Resolve the Performance bottlenecks.

What are the Prerequisites?

• Experience with SQL Server.
• Understanding of Database concept.
• Experience with SQL Server administration.
• Experience with Transact-SOL Programming.
• Knowledge of SQL Server performance tuning concept.

What are the objectives?
After completing this series of sessions, participants will be able to:

• Identify the System Performance Bottlenecks and Top Performance Killers in SQL Server.
• Analyze the SQL Server Performance metrics such as WaitStats, Latch Stats and etc.
• Use the SQL Server trace tools such as SQLTrace, Extended Events, SQL Profiler and Performance Monitor Data Collector Set.
• Analyze, Design and Deploy Indexes in order to support multiple queries within the database workload.
• Analyze and resolve Bookmark Lookup and Statistics related issues in Execution Plans.
• Understand the Performance Tuning exceptions and tricks within SQL Server environment.
• Analyze and resolve fragmentation, transaction blocking and deadlocks.
• Configure Database and Instance for the optimum performance.

Outlines for Session 1:

• The Performance Tuning Process.
• Performance Vs. Price.
• The Performance Baseline.
• Where to Focus Efforts in Tuning.
• The Top 11 SQL Server Performance Killers.
• Performance Monitor Tool.
• Analyze Hardware Resource Bottlenecks.
• Retrieve Performance Monitor Data by DMVs.
• Resolve Hardware Performance Bottlenecks.
• Analyze the Overall Performance of SQL Server.
• The Basics of the SQL Server Profiler Tool
• How to Analyze Costly Queries by SQL Server Profiler Tool
• How to Combine baseline Measurement with Data Collected by SQL Server Profiler
• How to Track Query Performance by DMV
• How to Analyze Effectiveness of Index and Join Strategies for the SQL Query
• How to Measure the Cost of SQL Query Using SQL Utilities