1 / 63

Managing SQL Server Performance

Managing SQL Server Performance. AtlantaMDF August 11, 2003 By Larry Ansley. Performance Maximization Plan In Only 4 Easy Steps. Denormalize Index Everything Use Full-Text Searches Liberally Get a Bigger, More Powerful System. Performance Areas Hardware. Memory CPU Storage Network.

eholly
Download Presentation

Managing SQL Server Performance

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. Managing SQL ServerPerformance AtlantaMDF August 11, 2003 By Larry Ansley

  2. Performance Maximization PlanIn Only 4 Easy Steps • Denormalize • Index Everything • Use Full-Text Searches Liberally • Get a Bigger, More Powerful System

  3. Performance AreasHardware • Memory • CPU • Storage • Network

  4. Performance AreasOperating System • Configuration • Version Upgrades

  5. Performance AreasOperating System • Configuration • File Management • Version Upgrades

  6. Performance AreasSQL Server • Configuration & Options • File Management • Version Upgrades

  7. Performance AreasDatabases • Configuration & Options • Tables • Indexes • File Management

  8. Performance AreasStored Procedures • Logic • Best Practices

  9. Performance Killers • Poor indexing • Inaccurate statistics • Excessive fragmentation • Non-reusable execution plans • Frequent recompilation • Excessive blocking and deadlocks • Poor query design

  10. Performance Killers • Poor database design • Improper cursor use • Ineffective connection pooling • Improper database log configuration

  11. Taming The Performance Killers • Planning • ER Diagrams • Data Modeling • Capacity Requirements • Procedure Case Analysis • Follow Best Practices in Development

  12. Taming The Performance Killers • Good Record Keeping • Logs • Baselines • Audits

  13. Performance BaselinesBenefits • Allows us to analyze resource bottlenecks • Allows us to troubleshoot by comparing system utilization patterns with pre-established baselines • Allows us to make accurate estimates in capacity planning and scheduling hardware upgrades

  14. Performance BaselinesBenefits • Aids us in identifying low utilization periods, when we can execute administrative activities • Helps us distinguish between performance perception and reality

  15. Performance BaselinesBaseline Tools • System Monitor (Key Resource Counters) • CPU – % Processor Time (85%) • CPU – Processor Queue Length (2) • Memory – Available Bytes (4 MB) • Memory – Pages / Sec (20) • Memory – Buffer Cache Hit Ratio ( 90%)

  16. Performance BaselinesBaseline Tools • System Monitor (Key Resource Counters) • Disk – % Disk Time (90%) • Disk – Avg. Disk Queue Length (number of spindles + 2) • Network – % Net Utilization (30% for Ethernet) • SQL Server – User Connections

  17. Performance BaselinesBaseline Tools • SQL Profiler • Record Traces for Major Processes • Duration, CPU, Reads, Writes

  18. Performance Audit • System Monitor • Hardware • Operating System • SQL Server Configuration • Database Settings • Indexes • Application and Transact-SQL

  19. Performance AuditSystem Monitor • Processor: % Processor Time • Recurring period exceeding 80% indicate CPU bottlenecks • System: Processor Queue Length • Recurring periods exceeding 2 per CPU indicate CPU bottlenecks • Consider Maximum Worker Threads setting

  20. Performance AuditSystem Monitor • Memory: Pages / Sec • Average around 0 over 24 hours • Average over 20 indicates memory bottleneck • Memory: Available Bytes • Should be greater than 5 MB • Dedicated SQL Servers attempt to maintain 4-10 MB free • Less than 5 MB indicates a memory bottleneck

  21. Performance AuditSystem Monitor • SQL Server Buffer: Buffer Cache Hit Ratio • For OLTP application, should exceed 90%, ideally around 99% • OLAP (Analysis Services) application work well with lower ratios • SQL Server General: User Connections • If this exceeds 255, Maximum Worker Threads setting should be increased to remain greater than user connections, thus avoiding thread sharing.

  22. Performance AuditSystem Monitor • Physical Disk: % Disk Time • Should run less than 55% • Recurring periods (10 minutes or more) exceeding 55% indicate I/O bottlenecks • Physical Disk: Avg. Disk Queue Length • Should run less than 2 per spindle • Recurring periods exceeding 2 indicate I/O bottlenecks

  23. Performance AuditHardware • Number of CPUs • CPU MHz • CPU L2 Cache Size • Physical Ram Amount • Total Amount of Available Drive Space on Server • Total Number of Physical Drives in Each Array

  24. Performance AuditHardware • Hardware vs. Software Raid • RAID Level of Each Array Used • Disk Fragmentation Level • Location of Operating System • Location of SQL Server Executables • Location of Swap File • Location of tempdb Database

  25. Performance AuditHardware • Location of System Databases • Location of User Databases • Location of Log Files • Number of Disk Controllers in Server • Type of Disk Controllers in Server • Size of Cache in Disk Controllers In Server • Is Write Cache in Disk Controller On or Off

  26. Performance AuditHardware • Speed of Disk Drives • How Many Network Cards Are in Server • Are the Network Cards Hard-Coded for Speed/Duplex • Are the Network Cards Attached to a Switch • Are All the Hardware Drivers Up-to-Date • Is This Physical Server Dedicated to SQL Server

  27. Performance AuditOperating System • Are the Disk Partitions NTFS 5.0 • Is “NTFS Data File Encryption and Compression” Turned Off • Is the Server Updated With the Latest Service Pack • Does the Server Have the Most Current, Microsoft-Certified Hardware Drivers

  28. Performance AuditOperating System • Is the Windows 2000 Server Configured as a Stand-Alone Server • Are the Physical Files on the Server Overly Fragmented • Is the “Application Response” Setting Set to “Optimize Performance” for “Background Services” • Has Security Auditing Been Turned On

  29. Performance AuditOperating System • How Large Is the Server’s PAGEFILE.SYS swap file • Have Unnecessary Services Been Turned Off • Have All Unnecessary Network Protocols Been Turned Off

  30. Performance AuditSQL Server Configuration

  31. Performance AuditSQL Server Configuration

  32. Performance AuditSQL Server Configuration

  33. Performance AuditSQL Server Configuration

  34. Performance AuditSQL Server Configuration

  35. Performance AuditSQL Server Configuration

  36. Performance AuditDatabase Configuration Settings

  37. Performance AuditDatabase Configuration Settings

  38. Performance AuditIndexes • Have you run the Index Tuning Wizard recently • Does every table in each database have a clustered index • Are any of the columns in any table indexed more than once • Are there any indexes that are not being used in queries

  39. Performance AuditIndexes • Are the indexes too wide • Do the tables that are joined have the appropriate indexes on the joined columns • Are the indexes unique enough to be useful • Are you taking advantage of covering indexes • How often are indexes rebuilt • What is your index fillfactor

  40. Performance AuditTransact-SQL • Does the Transaction-SQL code return more data than needed • Are cursors being used when they don’t need to be • Are Union and Union Select properly used • Is Select Distinct being used properly • Is the Where clause Sargable • Are temp tables used when they aren’t needed

  41. Performance AuditTransact-SQL • Are hints being properly used in queries • Are view unnecessarily being used • Are stored procedures being used whenever possible • Inside stored procedures, is Set NoCount On being used • Do any of your stored procedures start with sp_

  42. Performance AuditTransact-SQL • Are all stored procedures owned by DBO and referred to in the form of databaseowner.objectname • Are you using constraints or triggers for referential integrity • Are transactions being kept as short as possible

  43. Performance AuditApplications • Is the application using stored procedures, strings of T-SQL code, or using an object model, like ADO, to communicate with SQL Server • What method is the application using to communicate with SQL Server: DB-Lib, DAO, RDO, ADO, .Net • Is the application using ODBC or OLE DB to communicate with SQL Server

  44. Performance AuditApplications • Is the application taking advantage of connection pooling • Is the application properly opening, reusing, and closing connections • Is the T-SQL code being sent to SQL Server optimized for SQL Server, or is it generic SQL • Does the application return unnecessary data

  45. Performance AuditApplications • Is the application taking advantage of connection pooling • Is the application properly opening, reusing, and closing connections • Is the T-SQL code being sent to SQL Server optimized for SQL Server, or is it generic SQL • Does the application return unnecessary data

  46. Performance AuditApplications • Does the application keep transaction open when the user is modifying data

  47. Monitoring & Measuring Tools • Enterprise Manager • Query Analyzer • System Monitor • SQL Profiler • 3rd Party

  48. Monitoring & Measuring ToolsEnterprise Manager • Management / Current Activity • Process Info (sp_who2) • Locks / Process ID (sp_lock) • Locks / Object (sp_lock) • Management / SQL Server Logs

  49. Monitoring & Measuring ToolsQuery Analyzer • sp_who / sp_who2 • sp_lock • sp_spaceused • sp_monitor • Showplan_Text • Showplan_All • Set Statistics IO • Set Statistics Time • Set Statistics Profile

More Related