1. Simple Logic
  2. performance tuning

performance tuning

Engagement Details and Objective

SQL Server Performance Tuning Goals:
  • Instance Level Tuning

    Areas to Fix:
    Improper SQL Server configuration parameter
    Excessive load on storage/CPU/Memory
    Transaction/query slowness

  • SQL Tuning

    Areas to Fix:
    Query Full table scans
    Wrong Index scan
    Query hard parsing
    Locking issue
    Excessive load on storage/CPU/Memory
    Transaction/query slowness

  • Automate Proactive alerts and reports

    Automate Proactive alerts and reports setting to identify potential problems in future.

  • Database Level Tuning

    Areas to Fix:
    Improper database file size/structure
    I/O distribution Analysis
    SQL Server Resource Wait Stats Analysis
    Excessive space usage
    Transaction/query slowness

  • Server system Tuning

    Analyze systems CPU/Memory/Network and storage load after above goals
    Resource sizing if required.

  • Post-Performance benchmarking and issue troubleshooting

    Performance analysis and benchmarking after changes.

Database Performance Optimization


SECTION I: INSTANCE LEVEL TUNING
High Level Steps
  • Before changes checking locking, high CPU utilizing query, performance monitor counter & index fragmentation reports collection
  • Perform OS Level Hardening from SQL Point of View.
  • Instance level parameter changes.
  • Instance memory sizing
  • Log file space configuration
  • Temp DB and Data & Log file storage checking.
  • Checking datafile I/O

Team Responsible: MS-SQL Server DBA
Rollback – Revert parameters


Database Stats policy designing and Automation
High Level Steps
  • Stats last update history collection
  • New stats creation if required
  • Index stats information collection
  • Scripting and Automation for the same
  • Reporting and alert setting for stats
  • Post stats implementation, performance reports collection and comparison.
  • Auto stat creation & Auto stat updating at database level
  • Query Plan review and stats fixing according to optimizer plan.
  • Query plan stabilization

Team Responsible:MS-SQL Server DBA
Rollback –Revert to original stats & database stat configuration


SECTION II: DATABASE LEVEL TUNING
Index defragmentation and storage changes
High Level Steps
  • Generate index fragmentation report with required storage parameter.
  • Reorganization of heavily fragmented indexes.
  • Index rebuilding with required storage parameter
  • Minimizing log space used by the database log file to reduce VLF counts.

Rollback Plan: Revert object’s storage parameters and reorganize.


SECTION III: SQL TUNING
Indexing strategy changes
High Level Steps
  • Table re-indexing for top and contention causing Queries
  • Index creations as per new optimizer plan and query filters/logic
  • Existing index modifications if required for query tuning
  • Index Monitoring and listing of unwanted indexes.
  • Drop unwanted indexes

Team Responsible: MS-SQL Server DBA (With concurrence of Application Team)
Rollback Plan: Drop or recreate index

SQL Profiling
High Level Steps
  • Start using SQL server profiler for information collection
  • Use SQL server standard trace flags for improving performance

Team Responsible: MS-SQL Server DBA
Rollback Plan: Remove trace flags, stop profiler trace.

Analysis on top Queries which are candidate for Application/code changes
High Level Steps
  • Recommendations for application level code changes and for bind variables use.

Team Responsible: MS-SQL Server DBA – for giving recommendations and Application Team – For application feasibility/flow checks and code changes.
Rollback Plan: Revert application code changes

Note: This time will be majorly depend upon the Application Team response.


SECTION IV: SERVER TUNING
Server system review at OS, Storage and Network level
High Level Steps
  • Analyze systems CPU/Memory/Network and storage reports Resource sizing in case of genuine load.

Team Responsible: MS-SQL Server DBA, Server, Network and storage team.


SECTION V: PROACTIVE ALERTS/REPORTS
MS-SQL server Reports automation
High Level Steps
  • Generating automated system report
  • Generate alerts for outdated statistics
  • Automate the performance log collection
  • Threshold review of existing alerts and modifications
  • Monitoring reports through email notification
  • Documentation

Team Responsible: MS-SQL Server DBA


SECTION VI: Post Activity, Performance review and troubleshooting
High Level Steps
  • Thorough analysis on Index fragmentation, log space report, Performance counters
  • Performance comparison and benchmarking
  • Storage and server CPU/Memory performance analysis after changes
  • Work on End user’s complaints and inputs
  • Share the performance tuning/achievement report

Team Responsible:MS-SQL Server DBA, Server, WNTEL, Network, Application Team and End users


Section VII: Activity Report
High Level Steps
  • Team will capture before and after a state of the database and submit the activity report with the resolution steps
  • Attached the screen shots wherever possible


Section VIII: Post production Support
High Level Steps
  • Team will be available onsite for the first-month end cycle (After the activity) for a period of 3 days
    to capture the data and analysis of the behavior of the database
  • Team will be available remote for the second month end cycle (After the activity) for a period of 3 days to capture the data and analysis of the behavior of the database

AFTER ABOVE RESPECTIVE TASKS, WE WILL SHARE THE PERFORMANCE IMPROVEMENT REPORTS BEFORE FINAL ACTIVITY SIGN-OFF.