Workshop Outline

Introduction and Overview

Memory

• A dive into the memory utilization of SQL Server for both 32 and 64 bit systems that will cover the key components related to performance.
  • Task manager
  • How SQL Server utilizes memory
  • SQL Server memory settings
  • Memory related DMV’s
  • Memory specific counters
  • TokenAndPermUserStore
  • Signs of memory pressure

• SQL Server has its own scheduler of which we will explore why they chose this over using the OS scheduler and how that affects SQL Server performance. We will cover some of the DMV’s related to monitoring CPU activity, health and status.
  • Overview of the SQL OS and threading model
  • Utilizing DMV’s to:
    • Detect CPU pressure
    • Monitor overall CPU Health & status

Storage Subsystems & I/O

• In this section we will cover the essentials of I/O relating to SQL Server to include Raid Levels, different storage subsystems and how SQL Server makes I/O requests.
  • Raid Levels
  • SAN vs. DAS vs. NAS
  • SQL Server I/O

Lunch

Database Files & Filegroups

• Covers how to properly configure the number and size of files / filegroups in a database to optimize for performance and recoverability along with how Transaction Log files utilize VLFs.

TempDB

• Here we will discuss the main performance related issues associated with tempdb and how to address them. Including the dmv’s that allow us to see how tempdb is being used in terms of memory and space allocations.
  • Detecting and troubleshooting issues associated with TempDB
    • Internal contention
    • Page Allocations
    • TempDB specific counters

Break

File Stats

• We will cover about how to collect the metrics associated with the virtual file stats DMV. We will then see how to analyze the information and correlate the metrics collected with actual issues related to physical I/O.
  • Collecting the file stats metrics
  • Analyzing the file stats
  • Correlating physical I/O issues with proper solutions
  • Data & Log files

• We will cover how to collect the metrics associated with the Wait stats DMV. We will then see how to analyze the information with reports and go thru the top x wait types describing what they are and how to address them.
  
  • Collecting Wait Stats metrics
  • Analyzing the wait stats
  • Understanding the key wait types and determining proper actions

Perfmon / Sysmon

• This is a basic overview of the tool and how to use it effectively. We will cover the use of Log mode along with using relog & statman to automate the creation, managing and parsing of the log files. It will cover a basic set of counters that every good dba should be aware of with more specific ones covered in other modules. The use of PAL to parse & analyze be covered as well.
  • General overview
  • How to use Log mode
  • Automation
  • Base set of counters for Windows & SQL Server
  • Parsing the information with PAL

Break

Profiler / Trace / DMVs / RML

• This module is a high level overview of Profiler that will stress automating the process without Profiler and minimizing impact to performance. It will also explore various methods of parsing and analyzing the information to find the best ways to identify which queries are causing the most harm and which ones you should address first to maximize ROI.
  • Profiler
  • Profiler / Perfmon split view
  • SQL Trace
  • Automating Collections
  • Parsing and analyzing the data
  • RML Utilities

Lunch

Cached Query Plans

• Here we will start out with a quick overview of what a query plan is (more details in the query tuning module) and how that relates to the information in the plan cache. The relevant portions of the plan cache DMV’s will be explored in detail to help identify the trouble spots with plan executions. And finally we will spend a fair amount of time looking at how plan reuse works and what we can do to maximize plan reuse.
  • What is a query Plan
  • Detailed look at the plan cache
  • DMV’s associated with the procedure cache and plan executions
  • Plan reuse

Break

Blocking

• Basics of blocking & deadlocking with some scripts to see
  • Who is blocking and who is waiting
  • How adding an index or using snapshot isolation can help
  • Detecting blocking
  • Resolving blocking

Index Usage Statistics

• Covers each of the DMV’s associated with the usage / operations of each index along with the missing index DMV’s. This will cover the pertinent aspects of the following DMV’s:
  • sys.dm_db_index_operational_stats
  • sys.dm_db_index_usage_stats
  • sys.dm_db_missing_index_columns
    • sys.dm_db_missing_index_details
    • sys.dm_db_missing_index_group_stats
    • sys.dm_db_missing_index_groups
• Index related DMV’s
• Determining Index usage
• Finding missing indexes

Query Tuning

• This module will cover the basics of query tuning stressing the most common problems you are likely to encounter and how to address them including but not limited to:
  • Execution Plans
  • Query Optimizer
  • Analyzing Show Plans
    • Statistics IO
    • Graphical
    • Textual
    • XML

• Parameter sniffing
• Hints
• Optimize for
  • Plan Guides
  • Writing Search queries

Lunch

Index Tuning

• This will touch on the core aspects of indexes and how they are used by SQL Server in order to understand how to properly tune indexes for performance and ease of maintenance.
  • Clustered vs. Nonclustered Indexes
  • Compound Indexes
  • Covering Indexes
  Break
  
  • Filtered Indexes
  • Index Access Methods
  • Fragmentation – Effects and prevention

Event Adjourns