1 / 48

Chapter Six

Chapter Six. Optimizing and Troubleshooting Databases. Objectives. Use files and filegroups to optimize performance Discuss common RAID levels Design optimized indexes. Objectives. Manage database fragmentation Understand how SQL Server 2000 uses statistics and execution plans

brenna
Download Presentation

Chapter Six

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. Chapter Six Optimizing and Troubleshooting Databases

  2. Objectives • Use files and filegroups to optimize performance • Discuss common RAID levels • Design optimized indexes

  3. Objectives • Manage database fragmentation • Understand how SQL Server 2000 uses statistics and execution plans • Troubleshoot transactions and locking

  4. Optimizing and Troubleshooting Databases • Query optimizer • Analyzes queries prior to executing them in order to determine the most efficient method of execution • The result of this analysis is called an execution plan

  5. Optimizing Databases • Sound database schema and properly indexed tables will usually result in optimized performance • Proper placement of tables indexed across multiple physical hard drives will also reduce execution times for queries

  6. Placing Indexes in Tables Figure 6-1: Database with one file on one filegroup

  7. Filegroups and Tables Figure 6-2: Database with multiple data files residing on multiple, separate hard drives • Parallel scans • Process by which multiple CPUs are used to read data residing on multiple filegroups simultaneously

  8. Optimization through Index Placement • By default, indexes are stored on the same filegroup as the table on which they are defined but SQL Server 2000 allows nonclustered indexes to reside on different filegroups from their related table • A clustered index is stored in the actual data pages of a table and must reside on the same filegroup as the table on which it is defined

  9. Performance Enhancements through RAID • Redundant array of independent disks (RAID) • Hard drive configuration where multiple physical disk drives are grouped to create a single logical drive • RAID devices can be configured in a number of ways, each with its own benefits and limitations • There are several common RAID categories that are classified with a level number

  10. RAID 0 • RAID 0 is the simplest RAID level • Provides improved performance by using disk striping to efficiently distribute and manage the location of data across several hard drives • Disk striping • Process by which data is broken up and stored across several group hard drives • Stripes • Individual block of data used with disk striping

  11. RAID 0 Figure 6-3: RAID 0

  12. RAID 1 • RAID 1 provides basic hardware fault tolerance Figure 6-4: RAID 1

  13. RAID 5 • RAID 5 provides good read performance and fault tolerance • In a RAID 5 disk configuration, parity information is stored to recreate lost data • Parity information indicates whether a number is odd or even

  14. RAID 5 Figure 6-5: RAID 5

  15. Parity at Work Figure 6-6: Parity in RAID 5

  16. RAID 10 • RAID 10 combines mirroring from RAID 1 with striping from RAID 0 Figure 6-7: RAID 10

  17. Using RAID Arrays and Clustering • Node • A single computer running in a cluster configuration • Cluster group • Shared resource in a system configured for failover clustering

  18. Indexing Recommendations • When properly defined, indexes provide optimized performance in SQL Server 2000 • When an index is not used to locate data, the database engine executes a table scan • Table scan • Operation that searches for data in a table by starting with the first row and accessing each row after that until the data is found

  19. Understanding when Indexes Are Recommended • Types of queries that benefit from indexes: • Queries that perform an “exact match” search on some key value in a table column and return a small number of records • Queries that use a range of values to return rows • Queries that use a join operation to retrieve related information in more than one table • Queries that return rows in a particular sort order

  20. Recommendations for Designing Indexes • Common misconception with indexes is that “more is better” • Having too many indexes on a single table can seriously degrade the performance of write operations to the table

  21. Recommendations for Designing Indexes • Covered query • Query whose requested table columns are all stored in a single index • Narrow vs. wide indexes • Narrow indexes • Key off very few table columns • Wide indexes • Contain many columns from a table

  22. Page Splits and Fragmentation • Page split • Process of handling a new insertion to a full index or data page • Fragmentation • Inefficient use of physical storage that results from page splitting

  23. Avoiding Fragmentation • The easiest way to resolve a table fragmentation problem is to rebuild the clustered index on the table • Ways to rebuild indexes in SQL Server 2000: • Use the T-SQL DROP and CREATE commands • Use the DROP_EXISTING option with a CREATE statement • Use the DBCC DBREINDEX statement

  24. Fill Factors with Indexes • Fill factor • Attribute of an index that defines the amount of free space left on each page of the index when it is created or rebuilt • Only implemented when an index is created or rebuilt • Not maintained once the index is rebuilt

  25. Execution Plans and Statistics • Query optimizer • Finds most efficient way to execute database query • Execution plan • Step-by-step instructions for executing a query in the most efficient way • SQL Server 2000 allows storage of statistical information about the distribution of values in a column • When indexes are created, this information is captured and then used by the query optimizer to determine the cost of using a particular index to perform an operation

  26. Viewing Execution Plans • There are two ways to view the execution plan for a particular query • Query Analyzer tool • Provides intuitive GUI interface for viewing and analyzing execution plans • SET SHOWPLAN_ALL T_SQL statement • Can be used to display a text-based representation of the execution plan of a query

  27. Analyzing Execution Plans with Query Analyzer Figure 6-8: Display Estimated Execution Plan button Figure 6-9: GUI display of an execution plan in Query Analyzer

  28. Analyzing Execution Plans with Query Analyzer Figure 6-10: Additional information about an individual operation in an execution plan

  29. Analyzing Execution Plans with Query Analyzer • Additional information shown on window in Figure 6-10 consists of the following: • Physical and Logical operation • Individual operations used to process an operation • Estimated row count • Estimated number of rows returned by an operation • Estimated row size • Estimated size of the results in bytes

  30. Analyzing Execution Plans with Query Analyzer • Additional information shown on window in Figure 6-10 consists of the following (cont.): • Estimated I/O cost • Estimated I/O resources required to complete the operation • Estimated CPU cost • Estimated CPU resources required to complete the operation • Estimated number of executes • Estimated number of times that the operation will have to be executed to process the operation

  31. Analyzing Execution Plans with Query Analyzer • Additional information shown on window in Figure 6-10 consists of the following (cont.): • Estimated cost • Estimated cost of operation given as a percentage of total estimated cost of executing the query • Estimated subtree cost • Estimated cost of executing current operation and any other preceding operation • Argument • Any parameters supplied with original T-SQL query

  32. Viewing Execution Plans with T-SQL Figure 6-11: Viewing an execution plan with the SET SHOWPLAN_ALL ON statement

  33. Using Index Hints to Control Query Plan Operation • Hints • T-SQL directives used to manually control how execution plan is generated by query optimizer • Three common types of hints • Join hints • Specify type of join operation to be used by query optimizer • Query hints • Specify type of query operation to be used by query optimizer • Table hints • Specify way in which tables are accessed

  34. Execution Plan Caching • Procedure cache • Pool of memory allocated by SQL Server 2000 to store and reuse compiled execution plans • Database engine will check the cache every time a query is sent to determine if an execution plan already exists for the query

  35. Execution Plan Caching • Common actions that can cause execution plan to be recompiled: • Changing structure of a table or view • Regenerating statistics, either automatically or through a call to the UPDATE STATISTICS T_SQL statement • Dropping an index used by an execution from the database • Changing a significant amount of data in a table • Sp_recompile • System-stored procedure used to force stored procedures and triggers to regenerate a more current execution plan

  36. Diagnosing Database Problems • SQL Profiler • Monitoring tool that records operations that occur in an instance of SQL Server 2000 • Helpful for performing the following tasks: • Identifying poorly performing queries • Monitoring stored procedure activity • Monitoring locks and determining what is causing a deadlock • Monitoring individual user activity

  37. Diagnosing Database Problems • Trace • Specific session of monitoring performed with SQL Profiler • Each trace can be configured to capture different types of events and capture the events in a database table or trace file • Trace template • Set of configurations for a trace

  38. Diagnosing Database Problems Figure 6-12: Selecting event classes for a trace in SQL Server Profiler

  39. Diagnosing Database Problems Figure 6-13: Specifying data columns for a trace in SQL Profiler

  40. Diagnosing Database Problems • Monitoring process activity with Enterprise Manager • By using Enterprise Manager, you can effectively monitor the following types of activity: • Current users connected to a SQL Server 2000 instance • Process number and commands that users are running against a database • Various objects that are locked and the types of locks they hold

  41. Diagnosing Database Problems • Current activity information is accessed in Enterprise Manager by navigating to Management folder for a particular instance and expanding Current Activity item in the tree as shown in Figure 6-14 Figure 6-14: Accessing the monitoring capability of SQL Enterprise Manager

  42. Diagnosing Database Problems • The Process Info item shows the current user connections and activity • Each user has a unique number associated with each connection it has to a database • This number is known as a System Process Identification Number (spid)

  43. Diagnosing Database Problems Figure 6-15: Viewing current activity from Enterprise Manager

  44. Diagnosing Database Problems • SQL Server 2000 has two system-stored procedures that are helpful in monitoring process activity • Sp_who • Returns a snapshot of current user and process activity • Sp_lock • Shows current lock information much in the same way as Enterprise Manager

  45. Diagnosing Database Problems Figure 6-16: Viewing current activity from Query Analyzer

  46. Diagnosing Database Problems • Terminating blocking processes • When a process blocks another processes, the offending (blocking) process may need to be manually terminated to allow the blocked process to continue executing • Typically, this situation can happen if a transaction is not committed or if a query is poorly written and goes into an infinite loop

  47. Chapter Summary • SQL Server 2000 provides various methods for optimizing and troubleshooting database activity • Multiple hard drives can be leveraged to improve performance through proper placement of tables and indexes in filegroups • RAID disk solutions provide optimized hard drive performance for computers running SQL Server 2000

  48. Chapter Summary • Understanding execution plans and how the database engine creates them is important when troubleshooting poorly performing queries • Query optimizer chooses the most efficient indexes and fastest join processes based on the indexes available and the distribution statistics of the data within the indexes • Poorly performing queries should be identified and optimized to prevent bottlenecks, like blocking in a concurrent environment

More Related