Chapter 14: SQL Server Monitoring and Tuning

1
Chapter 14 SQL Server Monitoring and Tuning
2
Overview
P499

• Monitoring Databases and SQL Profiler
• Index Tuning and Database Partitioning

3
SQL Server Monitoring
P500-506

• SQL Profiler
• Choosing Events, Data Columns, and Filters
• Preparing to Run a Trace
• Controlling the Trace
• - Save to a Trace File or a Trace Log

4
Index Tuning and Database Partitioning
P511-515

• Index Tuning Overview
• Partitioning Overview

5
Index Tuning Overview
P511-514

• Keep Existing indexes and/or add indexed views
• Specify a workload sample
• Choose the table(s) to tune
• Implement suggestions now, schedule, or manually

6
Partitioning Overview
P514-515

• File and Disk Partitioning
• Separate database files and log files
• Implement RAID 0 (tempdb), 1 (log files), 5 or
  10 (database files)
• Federated Servers
• Hold Distributed Partition Views
• Clustered Servers
• Group of servers acting as one (active and
  passive nodes)

7
Review

• Monitoring Databases and SQL Profiler
• Index Tuning and Database Partitioning

8
Lab SQL Server Monitoring and Tuning
9
Lab SQL Server Monitoring and Tuning

• Exercise 1
• Pages 506-510 Capturing Events Using SQL
  Profiler
• Exercise 2
• Pages 515-518 Tuning Queries Using the Index
  Tuning Wizard