Assessment Report Outline

Return to Resume

Server Configuration:

Hardware: Adequacy of server hardware, SAN, and disk subsystems

  • Server Hardware
  • CPU & Cores (physical/virtual)
  • Memory
  • Network
  • I/O Subsystem

SQL Server Instance: Comprehensive analysis of production SQL Server instances

  1. Server Properties
  2. Instance Security
  3. Data Safety
  4. Data Maintenance
  5. File Layout
  6. Configuration Settings
    • clr enabled
    • cost threshold for parallelism
    • max degree of parallelism
    • max server memory (MB)
    • optimize for ad hoc workloads

Application Performance Characteristics: Evaluation of application performance

  1. Architecture
    • Performance Objectives
    • Workload Specifications
    • Execution Path & Environment
    • Resource Requirements
    • Processing Overhead
  2. Implementation

Database Performance Characteristics: Evaluation of database design and configuration

  • Efficient Schema Design
  • HA/DR Impacts
  • Partitioning
  • Compression
  • OLTP - OLAP - Reporting
  • Surface Area & Data Access

Query Performance Characteristics: Evaluation of performance based on query resources

  • Execution Plans
  • Profiler Results
  • Compilation
  • Execution Times
  • Wait Types
  • File I/O
  • Locking/Blocking
  • Transactions
  • Worst T-SQL
  • Deadlocks

Indexing: Evaluation of impact of indexes and statistics on query performance and resources

  • Missing Indexes
  • Unused Indexes
  • Inefficient/Bad Indexes
  • Stale Statistics
  • Index & Statistics Maintenance
  • Heaps

Query Analysis:

5 Most I/O Intensive Queries: T-SQL Statement

5 Most CPU Intensive Queries: T-SQL Statement

5 Longest Running Queries: T-SQL Statement

Recommendations:

Based on the findings of the comprehensive review.
Detailed plan of action with documentable results.