Database Systems Design Implementation and Management Eighth Edition
Database Systems: Design, Implementation, and Management Eighth Edition Chapter 11 Database Performance Tuning and Query Optimization
Objectives • In this chapter, you will learn: – Basic database performance-tuning concepts – How a DBMS processes SQL queries – About the importance of indexes in query processing – About the types of decisions the query optimizer has to make – Some common practices used to write efficient SQL code – How to formulate queries and tune the DBMS for optimal performance – Performance tuning in SQL Server 2005 Database Systems, 8 th Edition 2
11. 1 Database Performance-Tuning Concepts • Goal of database performance is to execute queries as fast as possible • Database performance tuning – Set of activities and procedures designed to reduce response time of database system • All factors must operate at optimum level with minimal bottlenecks • Good database performance starts with good database design Database Systems, 8 th Edition 3
Database Systems, 8 th Edition 4
Performance Tuning: Client and Server • Client side – Generate SQL query that returns correct answer in least amount of time • Using minimum amount of resources at server – SQL performance tuning • Server side – DBMS environment configured to respond to clients’ requests as fast as possible • Optimum use of existing resources – DBMS performance tuning Database Systems, 8 th Edition 5
DBMS Architecture • All data in database are stored in data files • Data files – Automatically expand in predefined increments known as extends – Grouped in file groups or table spaces • Table space or file group: – Logical grouping of several data files that store data with similar characteristics Database Systems, 8 th Edition 6
Basic DBMS architecture Database Systems, 8 th Edition 7
DBMS Architecture (continued) • Data cache or buffer cache: shared, reserved memory area – Stores most recently accessed data blocks in RAM • SQL cache or procedure cache: stores most recently executed SQL statements – Also PL/SQL procedures, including triggers and functions • DBMS retrieves data from permanent storage and places it in RAM Database Systems, 8 th Edition 8
DBMS Architecture (continued) • Input/output request: low-level data access operation to/from computer devices, such as memory, hard disks, videos, and printers • Data cache is faster than data in data files – DBMS does not wait for hard disk to retrieve data • Majority of performance-tuning activities focus on minimizing I/O operations • Typical DBMS processes: – Listener, User, Scheduler, Lock manager, Optimizer Database Systems, 8 th Edition 9
Database Statistics • Measurements about database objects and available resources – Tables, Indexes, Number of processors used, Processor speed, Temporary space available • Make critical decisions about improving query processing efficiency • Can be gathered manually by DBA or automatically by DBMS – UPDATE STATISTICS table_name [index_name] – Auto-Update and Auto-Create Statistics option • 資料庫屬性 -> 自動更新統計資料 • 資料庫屬性 -> 自動建立統計資料 Database Systems, 8 th Edition 10
Database Systems, 8 th Edition 11
Ch 08: dbcc show_statistics (customer, PK__CUSTOMER__24927208 ) Ch 08: dbcc show_statistics (customer, CUS_UI 1) 補充 SQL Server 2005 Database Systems, 8 th Edition 12
11. 2 Query Processing • DBMS processes queries in three phases – Parsing • DBMS parses the query and chooses the most efficient access/execution plan – Execution • DBMS executes the query using chosen execution plan – Fetching • DBMS fetches the data and sends the result back to the client Database Systems, 8 th Edition 13
Database Systems, 8 th Edition Query Processing 14
SQL Parsing Phase • Break down query into smaller units • Transform original SQL query into slightly different version of original SQL code – Fully equivalent • Optimized query results are always the same as original query – More efficient • Optimized query will almost always execute faster than original query Database Systems, 8 th Edition 15
SQL Parsing Phase (continued) • Query optimizer analyzes SQL query and finds most efficient way to access data – Validated for syntax compliance – Validated against data dictionary • Tables, column names are correct • User has proper access rights – Analyzed and decomposed into more atomic components – Optimized through transforming into a fully equivalent but more efficient SQL query – Prepared for execution by determining the execution or access plan Database Systems, 8 th Edition 16
SQL Parsing Phase (continued) • Access plans are DBMS-specific – Translate client’s SQL query into series of complex I/O operations – Required to read the data from the physical data files and generate result set • DBMS checks if access plan already exists for query in SQL cache • DBMS reuses the access plan to save time • If not, optimizer evaluates various plans – Chosen placed in SQL cache Database Systems, 8 th Edition 17
Database Systems, 8 th Edition 18
SQL Execution and Fetching Phase • All I/O operations indicated in access plan are executed – Locks acquired – Data retrieved and placed in data cache – Transaction management commands processed • Rows of resulting query result set are returned to client • DBMS may use temporary table space to store temporary data – The server may send only the first 100 rows of 9000 rows Database Systems, 8 th Edition 19
Query Processing Bottlenecks • Delay introduced in the processing of an I/O operation that slows the system – CPU – RAM – Hard disk – Network – Application code Database Systems, 8 th Edition 20
11. 3 Indexes and Query Optimization • Indexes – Crucial in speeding up data access – Facilitate searching, sorting, and using aggregate functions as well as join operations – Ordered set of values that contains index key and pointers • More efficient to use index to access table than to scan all rows in table sequentially Database Systems, 8 th Edition 22
Indexes and Query Optimization • Data sparsity: number of different values a column could possibly have • Indexes implemented using: (課本 p. 453) – Hash indexes – B-tree indexes: most common index type. Used in tables in which column values repeat a small number of times. The leaves contain pointers to records. It is self-balanced. – Bitmap indexes: 0/1 • DBMSs determine best type of index to use – Ex: CUST_LNAME with B-tree and REGION_CODE with Bitmap indexes Database Systems, 8 th Edition 23
Database Systems, 8 th Edition B-tree and bitmap index representation 24
SELECT CUS_NAME FROM CUSTOMER WHERE CUS_STATE=‘FL’ Requires only 5 accesses to STATE_INDEX, 5 accesses to CUSTOMER Index Representation for the CUSTOMER table 25
11. 4 Optimizer Choices • Rule-based optimizer – Preset rules and points – Rules assign a fixed cost to each operation • Cost-based optimizer – Algorithms based on statistics about objects being accessed – Adds up processing cost, I/O costs, resource costs to derive total cost Database Systems, 8 th Edition 26
Example SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME, V_STATE FROM PRODUCT P, VENDOR V WHERE P. V_CODE=V. V_CODE AND V. V_STATE=‘FL’; • With the following database statistics: – The PRODUCT table has 7000 rows – The VENDOR table has 300 rows – 10 vendors come from Florida – 1000 products come from vendors in Florida Database Systems, 8 th Edition 27
Database Systems, 8 th Edition 28
Example • Assume the PRODUCT table has the index PQOH_NDX in the P_QOH attribute SELECT MIN(P_QOH) FROM PRODUCT could be resolved by reading only the first entry in the PQOH_NDX index Database Systems, 8 th Edition 29
Using Hints to Affect Optimizer Choices • Optimizer might not choose best plan • Makes decisions based on existing statistics – Statistics may be old – Might choose less efficient decisions • Optimizer hints: special instructions for the optimizer embedded in the SQL command text Database Systems, 8 th Edition 30
Oracle 版本 Database Systems, 8 th Edition 31
SQL Server Query Hints Example select o. customerid, companyname from orders as o inner MERGE join customers as c on o. customerid = c. customerid select o. customerid, companyname from orders as o inner HASH join customers as c on o. customerid = c. customerid select o. customerid, companyname from orders as o inner LOOP join customers as c on o. customerid = c. customerid select city, count(*) from customers group by city OPTION (HASH GROUP) MS SQL Server 的語法請參考: http: //msdn. microsoft. com/en-us/library/ms 187713. aspx Database Systems, 8 th Edition 32
11. 5 SQL Performance Tuning • Evaluated from client perspective – Most current relational DBMSs perform automatic query optimization at the server end – Most SQL performance optimization techniques are DBMS-specific • Rarely portable • Majority of performance problems related to poorly written SQL code • Carefully written query usually outperforms a poorly written query Database Systems, 8 th Edition 33
Index Selectivity • Indexes are used when: – Indexed column appears by itself in search criteria of WHERE or HAVING clause – Indexed column appears by itself in GROUP BY or ORDER BY clause – MAX or MIN function is applied to indexed column – Data sparsity on indexed column is high • Index selectivity is a measure of how likely an index will be used in query processing Database Systems, 8 th Edition 34
Index Selectivity (continued) • General guidelines for indexes: – Create indexes for each attribute in WHERE, HAVING, ORDER BY, or GROUP BY clause – Do not use in small tables or tables with low sparsity – Declare primary and foreign keys so optimizer can use indexes in join operations – Declare indexes in join columns other than PK/FK Database Systems, 8 th Edition 35
Conditional Expressions • Normally expressed within WHERE or HAVING clauses of SQL statement • Restricts output of query to only rows matching conditional criteria Database Systems, 8 th Edition 36
• Common practices for efficient SQL: – Use simple columns or literals in conditionals • Avoid functions – Numeric field comparisons are faster • than character, date, and NULL comparisons – Equality comparisons faster than inequality • —the slowest is “LIKE” comparison – Transform conditional expressions to use literals – Write equality conditions first – AND: Use condition most likely to be false first – OR: Use condition most likely to be true first – Avoid NOT Database Systems, 8 th Edition 37
11. 6 Query Formulation • Identify what columns and computations are required (p. 459) – Expressions – Aggregate functions – Granularity of raw data required • Identify source tables • Determine how to join tables • Determine what selection criteria is needed – Simple comparison? IN? Nested Comparison? HAVING • Determine in what order to display output Database Systems, 8 th Edition 38
11. 7 DBMS Performance Tuning • Includes managing DBMS processes in primary memory and structures in physical storage • DBMS performance tuning at server end focuses on setting parameters used for: – Data cache: large enough – SQL cache: same query may be submitted by many users – Sort cache – Optimizer mode • Cost-based or Rule-based Database Systems, 8 th Edition 39
DBMS Performance Tuning • Some general recommendations for creation of databases: – Use RAID (Redundant Array of Independent Disks) to provide balance between performance and fault tolerance – Minimize disk contention • At least with the following table spaces: system table, user table, index table, temporary table, rollback segment table – Put high-usage tables in their own table spaces – Assign separate data files in separate storage volumes for indexes, system, high-usage tables • Index operations will not conflict with data and data dictionary, can use different disk block size Database Systems, 8 th Edition 40
DBMS Performance Tuning • Some general recommendations for creation of databases: (continued) – Take advantage of table storage organizations in database • An indexed organized table stores the end user table and the index table in consecutive locations on permanent storage – Partition tables based on usage – Use denormalized tables where appropriate – Store computed and aggregate attributes in tables Database Systems, 8 th Edition 41
Common RAID Configurations Database Systems, 8 th Edition 42
11. 8 Query Optimization Example • Example illustrates how query optimizer works • Based on QOVENDOR and QOPRODUCT tables • Uses Oracle SQL*Plus (Skip) Database Systems, 8 th Edition 43
Database Systems, 8 th Edition 請參考以下 SQL Server 的講義 44
Database Systems, 8 th Edition 45
Check the differences in query plan: 1. Before UPDATE STATISTICS QOVENDOR 2. After UPDATE STATISTICS QOVENDOR 3. CREATE INDEX QOV_NDX 1 on QOVENDOR (V_AREACODE) UPDATE STATISTICS QOVENDOR 4. CREATE INDEX QOV_NDX 2 on QOVENDOR (V_NAME) UPDATE STATISTICS QOVENDOR Database Systems, 8 th Edition 46
Database Systems, 8 th Edition 47
Database Systems, 8 th Edition 48
Database Systems, 8 th Edition 49
Database Systems, 8 th Edition 50
Check the differences in query plan: 1. Before UPDATE STATISTICS QOPRODUCT 2. After UPDATE STATISTICS QOPRODUCT 3. CREATE INDEX QOP_NDX 2 ON QOPRODUCT(P_PRICE) UPDATE STATISTICS QOPRODUCT Database Systems, 8 th Edition 51
Summary • Database performance tuning – Refers to activities to ensure query is processed in minimum amount of time • SQL performance tuning – Refers to activities on client side to generate SQL code • Returns correct answer in least amount of time • Uses minimum amount of resources at server end • DBMS architecture represented by processes and structures used to manage a database Database Systems, 8 th Edition 53
Summary (continued) • Database statistics refers to measurements gathered by the DBMS – Describe snapshot of database objects’ characteristics • DBMS processes queries in three phases: parsing, execution, and fetching • Indexes are crucial in process that speeds up data access Database Systems, 8 th Edition 54
Summary (continued) • During query optimization, DBMS chooses: – Indexes to use, how to perform join operations, table to use first, etc. • Hints change optimizer mode for current SQL statement • SQL performance tuning deals with writing queries that make good use of statistics • Query formulation deals with translating business questions into specific SQL code Database Systems, 8 th Edition 55
13 -6 使用 SQL Server Management Studio 管理 具建立索引
使用 SQL Server Management Studio 管理 具建立索引
使用 SQL Server Management Studio 管理 具建立索引
使用 SQL Server Management Studio 管理 具建立索引
使用 SQL Server Management Studio 管理 具建立索引
- Slides: 79