www.ciquery.com

SQL Server Tuning Targets

There are many approaches to SQL tuning - Ratio-based analysis,Bottleneck analysis & Workload analysis to name a few. This section describes an efficient approach to SQL 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 tuning SQL, there are general guidelines that every SQL Server DBA follows in order to improve the overall 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 deceptively simple, but these tasks 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 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. Other articles section will provide a closer look at the goals listed above as well as how they simplify SQL tuning.

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here