Search
Subscribe
Categories
- Backup and Restore
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- Deadlocks
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Jobs
- Links
- Locking
- Memory
- Monitor Performance
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Cachestore
- SQL Error Logs
- SQL Optimizer
- SQL problems
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- Virtualization
- Wait Stats
Recent Posts
- How to check Operating System type with Powershell
- ALTER TRACE and developers
- Predict ALTER INDEX REORGANIZE finish time
- Cannot connect to SQL Server
- Timeout occurred while waiting for latch: class 'LOG_MANAGER'
- The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
- How to create a SQL Server Security Audit
- Cannot use Large Page Extensions: lock memory privilege was not granted
- Compare two files with Powershell
- Export-CSV Powershell
SQL Server performance tuning has different approaches - such as Ratio-based analysis,Bottleneck analysis & Workload analysis. This section describes an efficient approach to SQL Server tuning based on current system load. Once the "best" overall optimization is achieved, we drill-down into the specific cases of sub-optimal SQL, and change their execution plans with SQL Indexes.
Despite the complexity of SQL Server tuning , there are general guidelines that SQL Server DBAs follow to improve the performance of their SQL Server system. The goals of SQL tuning are simple:
- Tune subqueries to eliminate redundancy
- Analyse optimal JOIN techniques
- Replace unnecessary large-table full-table scans with useful indexes.
- Analyse optimal index usage
These goals may seem simple, but comprise 80 percent of SQL tuning. They do not require an indepth understanding of SQL Server internals.
If required, the SQL can be tuned regularly, but if the optimizer is not fed with the correct SQL statistics, the optimizer may not make the correct decisions.
Before tuning, it is important to ensure that statistics are available and that they are current.Even though you may have the Auto Update Statistics Property set at true , it will only kick in when changes are being made. A simple "sp_updatestats" will do the job.
Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |