m_13_1.00 performance tuning with demos and labs 2012.pdf
TRANSCRIPT
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
1/12
Module 13:Performance Tuning
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
2/12
Overview
Performance tuning methodologies
Instance level
Database level
Application levelOverview of tools and techniques for performanceanalysis and tuning
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
3/12
Tuning Methodologies Tuning While Building
The Tuning While Building methodology offers aproactive approach to performance tuning
These are the steps:
1. Tune the business rules
2. Tune the data design
3. Tune the application
4. Tune the instance
5. Tune the underlying platform
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
4/12
Tuning Methodologies Performance Tuning
Performance tuning is an ongoing, interactive processPerformance Improvement Method offers steps inimproving 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 effects of the changes
If unsuccessful, repeat previous two steps
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
5/12
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 aswell as to reduce network traffic
Tune Logical Structure of Database Leverage supporting schemaobjects
Example: choose appropriate indexes
Tune Database Operations
Find statements that consume most resources Use statisticsavailable 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
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
6/12
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
Instance Tuning
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
7/12
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, executionplans and several other statistics to aid in tuning SQL and T-SQL
Distributed Replay assess impact of hardware and OS upgrades and SQLServer tuning
Performance Data Collector fully integrated tool that collects a variety ofperformance 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 queriesto resolve performance issues
Database Engine Tuning Advisor database physical design tool to
optimize indexes and partitioning
Performance MonitorWindows tool that records counters at hardware,operating system, database, and application levels
Task Manager used for initial review of high-level counters on thesystem, including utilization at a process level
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
8/12
Demonstration: Data Compression
In this demonstration you will learn to:
Use the Compression Wizard to benefitfrom ROW or PAGE type options.
Understand how to calculate
compression savings with the wizard orwith scripts
Implement compression with the wizardor with scripts.
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
9/12
Demonstration: SQL Server Profiler
In this demonstration you will learn to:
Use the SQL Server Profiler to captureevents within SQL Server as it is beingused.
Choose the type of events to monitor
Generate script templates for traces.
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
10/12
Demonstration: Hints (T-SQL)
In this demonstration you will learn to:Enforce a HASH JOIN in a query.
Benefit from the OPTIMIZE FOR hint.
Override the row locking default with aTABLOCK hint.
Remove all shared locks with theNOLOCK hint.
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
11/12
Demonstration: Plan Guides
In this demonstration you will learn to:Override an existing query with a PlanGuide.
Use the SQL Server 2012 interface to
implement Plan Guides.
-
7/27/2019 M_13_1.00 Performance Tuning With Demos and Labs 2012.pdf
12/12
Review
We learned performance tuning methodologies, including TuningWhile Building to be used starting with application design processand Performance Improvement Method to be used to tuneproduction systems
We discussed steps in tuning an application, including tuningapplication design, access paths, logical structure, and operationsat the database
We went over steps in tuning an instance, including tuning memoryallocation, resource contention, and I/O and physical structures
We were introduced to a set of tools available in SQL Server andMicrosoft Windows that aid in tuning at hardware, operating system,database, and application levels ,and achieving overall optimalperformance