1. Simple Logic
2. performance tuning

performance tuning

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.