1 / 12

Module 13: Performance Tuning

Module 13: Performance Tuning. Overview. Performance tuning methodologies Instance level Database level Application level Overview of tools and techniques for performance analysis and tuning. Tuning Methodologies — Tuning While Building.

elia
Download Presentation

Module 13: Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 13: Performance Tuning

  2. Overview • Performance tuning methodologies • Instance level • Database level • Application level • Overview of tools and techniques for performance analysis and tuning

  3. Tuning Methodologies — Tuning While Building • The Tuning While Building methodology offers a proactive approach to performance tuning • These are the steps: • Tune the business rules • Tune the data design • Tune the application • Tune the instance • Tune the underlying platform

  4. Tuning Methodologies — Performance Tuning • Performance tuning is an ongoing, interactive process • Performance Improvement Method offers steps in improving performance of a system in production: • Define the problem • Examine the host system and the Oracle statistics • Examine documentation to help identify the problem • Generate a prognosis based on captured data • Propose a course of action and implement the changes • Evaluate the causes of the changes • If unsuccessful, repeat previous two steps

  5. Application Tuning • Tune Application Design — Leverage RDBMS features • Example: Perform calculations and summaries on the database before fetching the results so as to leverage database resources as well as to reduce network traffic • Tune Logical Structure of Database — Leverage supporting schema objects • Example: choose appropriate indexes • Tune Database Operations • Find statements that consume most resources — Use statistics available in system catalog, such as parses, CPU time, physical reads • Tune the statements to use fewer resources — Use coding standards and tools to analyze and tune SQL • Tune Access Paths — Aid Optimizer in finding optimal access path, creating indexes, clustered indexes, and providing optimizer hints

  6. Demonstration 1: Data Compression In this demonstration you will learn to: • Use the Compression Wizard to benefit from ROW or PAGE type options. • Understand how to calculate compression savings with the wizard or with scripts • Implement compression with the wizard or with scripts.

  7. Demonstration 2: SQL Server Profiler In this demonstration you will learn to: • Use the SQL Server Profiler to capture events within SQL Server as it is being used. • Choose the type of events to monitor • Review the activities by Replaying the trace at a later time. • Generate script templates for traces.

  8. Demonstration 3: Hints (T-SQL) In this demonstration you will learn to: • Enforce a MERGE JOIN in a query. • Benefit from the OPTIMIZE FOR hint. • Override the row locking default with a TABLOCK hint. • Remove all shared locks with the NOLOCK hint.

  9. Demonstration 4: Plan Guides In this demonstration you will learn to: • Override an existing query with a Plan Guide. • Use the new SQL Server 2008 interface to implement Plan Guides.

  10. Instance Tuning • Tune Memory Allocation • Proper configuration and use of the caches that make up the SGA (Oracle) and memory pool (SQL Server) • Tune I/O and Physical Structures • Separation of files based on access • Distribution of I/O load across files and devices • Tune Resource Contention • Reduce and/or eliminate contention for locks, latches, block (page) headers, and queues

  11. SQL Server Performance Tuning Tools • Profiler — records detailed information on activity on the database server, including I/O statistics, CPU statistics, index and table scans, execution plans and several other statistics to aid in tuning SQL and T-SQL • Performance Data Collector — fully integrated tool that collects a variety of performance metrics on one or more SQL Servers and stores the data in a central data warehouse • Build custom reports showing trends • Query Editor in SSMS — graphical tool used to execute and debug queries to resolve performance issues • Database Engine Tuning Advisor — database physical design tool to optimize indexes and partitioning • Replaces Index Tuning Wizard from previous SQL Server releases • Performance Monitor —Windows tool that records counters at hardware, operating system, database, and application levels • Task Manager — used for initial review of high-level counters on the system, including utilization at a process level

  12. Review • We learned performance tuning methodologies, including ‘Tuning While Building’ to be used starting with application design process and ‘Performance Improvement Method’ to be used to tune production systems • We discussed steps in tuning an application, including tuning application design, access paths, logical structure, and operations at the database • We went over steps in tuning an instance, including tuning memory allocation, resource contention, and I/O and physical structures • We were introduced to a set of tools available in SQL Server and Microsoft Windows that aid in tuning at hardware, operating system, database, and application levels, and achieving overall optimal performance

More Related