- +91 8291708207
- enquiry@simplelogic.in
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.