1 / 32

SQL Server Performance Tuning Concepts

SQL Server Performance Tuning Concepts. MDCFUG September 13, 2005 Jeremy Kadlec of Edgewood Solutions jeremyk@edgewoodsolutions.com | 410.591.4683. Session Agenda. Jeremy Kadlec and Edgewood Solutions Overview, challenges and options Performance tuning methodology

dafydd
Download Presentation

SQL Server Performance Tuning Concepts

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. SQL Server Performance Tuning Concepts MDCFUG September 13, 2005 Jeremy Kadlec of Edgewood Solutions jeremyk@edgewoodsolutions.com | 410.591.4683 © Edgewood Solutions 2005

  2. Session Agenda • Jeremy Kadlec and Edgewood Solutions • Overview, challenges and options • Performance tuning methodology • Performance management continuum • Critical performance issues • Performance Monitor/SQL Profiler • Q & A, summary and thank you © Edgewood Solutions 2005

  3. Jeremy Kadlec • Edgewood Solutions (www.edgewoodsolutions.com) • Customer focused SQL Server solutions • Planning, Audits, Integration, Training, Products • Performance Tuning, Administration, Development, Upgrades, Disaster Recovery, Database Auditing • Principal Database Engineer • jeremyk@edgewoodsolutions.com • 410.591.4683 • Author of numerous SQL Server resources • www.edgewoodsolutions.com/resources/articles.asp • SearchSQLServer.com – Ask the Experts • The Rational Guide to IT Project Management • NOVA SQL Co-Leader – www.novasql.com © Edgewood Solutions 2005

  4. People, Process, Technology Documented and practiced processes to analyze, prioritize, develop, test, implement and learn Talented team with architecture, hardware, development and database expertise People Process Technology Intuitive tools with the ability to make informed decisions and accurate corrections efficiently © Edgewood Solutions 2005

  5. Performance Tuning Best Practices • Focus on performance needs from the project scope to maintenance • Design and develop for high performance • Hardware, Windows, SQL Server and application • System baseline with ongoing comparisons • Monitor, analyze, alert and report • Solidified change management process • Properly designate permissions based on duties • Work towards continuous improvements © Edgewood Solutions 2005

  6. Performance Tuning Basics • Art and a science • People, Processes and Technology • Few simple problems, typically complex based on application • Holistic view needed • Goal – Accurate and efficient correction without any biases Perf Tuning - Circle of Life Learn Analyze Implement Prioritize Test Develop © Edgewood Solutions 2005

  7. Performance Tuning Challenges • Difficult to create a baseline and compare over time to current status • Exorbitant amount of data to manage and analyze = ‘analysis paralysis’ • Multiple tools to manage the tiers and they are not compatible • Difficult to distinguish the issue at an object level or tier = ‘finger pointing’ © Edgewood Solutions 2005

  8. Challenges - Continued • Cannot further impact the performance of the production system • Understand production, not simulation • Need for real time and historical view of performance data in a single system • Record of changes over time and subsequent impact once implemented • Throw hardware at the problem as a quick fix © Edgewood Solutions 2005

  9. Performance Tuning Needs • Full life cycle tool for application development and production support • Quickly determine the root cause and focus resources for efficient correction • No additional overhead for system analysis and short learning curve • Meaningful data for decision making • Automated, proactive and real-time © Edgewood Solutions 2005

  10. Performance Tuning Continuum SQL Server Performance Tuning Process Automation Automated Manual Educated Guess Hunt and Peck Tool Set People: Process: Technology: Entire company Reactive approach No tools Entire company Reactive approach Disjointed tools Entire company Proactive approach Integrated tools © Edgewood Solutions 2005

  11. Educated Guess • Users notify Help Desk of system issues • Help desk scrambles IT to find the problem • IT frantically searches for the problem • Network, Windows, SQL Server, front end application, logs, etc • Unable to find issue  report to Help Desk • User escalation to management • IT monitor for symptoms and make changes to benefit the users, but cannot validate Problem = Lack of information © Edgewood Solutions 2005

  12. Hunt and Peck Approach • Ask users where problems exist • Monitor SQL Server to capture data • Review data to determine issues • Change SQL Server based on data • Re-design, code changes, indexing, etc. • Monitor to determine improvement Problem = Information Overload © Edgewood Solutions 2005

  13. 24x7 Performance Monitoring • Install, configure and baseline • Review data from integrated tools • Current and historical view of system • Proactively and intuitively review • Focus IT Team on objects requiring the most resources • Correct and validate improvement © Edgewood Solutions 2005

  14. Enterprise Manager Overview – Primary tool to manage all SQL Server functionality across the enterprise Features – Ability to manage database processes, locking, indexes, configurations, etc. © Edgewood Solutions 2005

  15. Performance Monitor Overview – Capture a macro view of the servers with the ability to configure counters with specific sample rates save to a log file or real time monitor Counters • Memory, Processors • SQL Server • Network Activity, Disk Drives • System Statistics (Threads, Context Switching, Queuing, etc.) Samples – Secs to mins © Edgewood Solutions 2005

  16. Memory Page Reads/sec Page Writes/sec Page Input/sec Page Output/sec Network Interface Bytes Received/sec Bytes Sent/sec Bytes Total/sec Current Bandwidth Output Queue Length Objects All Paging File All Physical Disk – set ‘disk perf – y’ in DOS and reboot % Disk Read Time % Disk Write Time % Idle Time Avg Disk Bytes/Read Avg Disk Bytes/Transfer Avg Disk Bytes/Write Avg Disk Queue Length Current Disk Queue Length Process % Privileged Time % Processor Time % User Time Processor % Privileged Time % Processor Time % User Time Performance Monitor Counters © Edgewood Solutions 2005

  17. Server Work Queues Active Threads Available Threads Queue Length Total Bytes/sec Total Operations/sec SQLServer:Access Methods Full Scans Page Splits/sec Table Lock Escalations/sec SQLServer:Cache Manager Cache Hit Ratio - _Total Cache Pages - _Total SQLServer:Databases Transactions/sec SQLServer:General Statistics Logins/sec Logouts/sec User Connections SQLServer:Locks Number of Deadlocks/sec Performance Monitor Counters © Edgewood Solutions 2005

  18. SQL Server Profiler Overview – Micro view of all SQL Server transactions saved to a log file or database table Filters – Ability to capture a subset of the transactions based on the transaction type, user, application, etc. Concerns – High system overhead © Edgewood Solutions 2005

  19. Cursors CursorOpen CursorExecute CursorClose Errors and Warnings Hash Warning Missing Column Statistics Locks Lock:Deadlock Lock:Timeout TSQL Unprepare SQL Parallelism Degree of Parallelism – All Counters Execution Plan Show Plan All Show Plan Statistics Show Plan Text Stored Procedure SP:Starting SP:Completed SP:Recompile SP:StmtCompleted SP:StmtStarting SQL Profiler Event Data © Edgewood Solutions 2005

  20. Query Analyzer – Query Plan Overview – Ability to graphically review the query execution plan © Edgewood Solutions 2005

  21. SQL Server Objects © Edgewood Solutions 2005

  22. Common SQL Server Performance Problems © Edgewood Solutions 2005

  23. High CPU Utilization • Identification • Guess – Task Manager figures • Hunt – Perfmon counters • 24x7 – CPU usage by time, statement • Resolution • Add additional CPUs • Identify statement(s) with high CPU • Move processes to another server or to off peak times © Edgewood Solutions 2005

  24. High Disk I/O • Identification • Guess – Disk drive lights or drive churning • Hunt – Avg Disk Queue Length, % Disk Time • 24x7 – Review IO wait types and consumption • Resolution • Add additional physical drives • Separate tables, indexes, file groups • Separate databases on physical disks • Appropriate RAID (database 1, 10, 5 - log 1) • Add additional indexes and/or re-index tables © Edgewood Solutions 2005

  25. Poor Performing Statements • Identification • Guess – User perception and input • Hunt – Profiler statement analysis • 24x7 – Statements by resource, time, user • Resolution • Review database design and query plans • Review table access order for JOINs • Recommend indexes based on data access • Short transactions with regular commits © Edgewood Solutions 2005

  26. The Index Impact • Identification • Guess – User perception and input • Hunt – Review query plans for entire application • 24x7 – Index recommendations • Resolution • Use Index Tuning Wizard • CRUD chart to determine needed indexes • Review code to determine columns in JOIN, WHERE, ORDER BY, GROUP BY, etc clauses • Leverage correct index based on needs • Maintain indexes and statistics per object © Edgewood Solutions 2005

  27. Unknown SQL Server Changes • Identification • Guess – Broken application • Hunt – Query sysobjects • 24x7 – Schema change report • Resolution • Appropriate security based on duties • Solidified change management process • Open communication among the team © Edgewood Solutions 2005

  28. SQL Server Trending • Identification • Guess – Change in user complaints • Hunt – Perfmon and Profiler changes • 24x7 – Performance metrics over time • Benefits • Proactive approach for future planning • Justification for hardware and software • Capacity planning © Edgewood Solutions 2005

  29. Edgewood Offerings Customer centric solutions with beneficial results Couple products and services for a complete solution Training Auditing Planning Integration © Edgewood Solutions 2005

  30. Additional Resources • Additional Performance Tuning Recommendations • Veritas’s Indepth for SQL Server • www.edgewoodsolutions.com/partners/veritas.asp • Idera’s Diagnostic Manager • www.edgewoodsolutions.com/partners/idera.asp • Edgewood Offerings • Reach the Performance Tuning Pinnacle • www.edgewoodsolutions.com/whitepaper.asp • Evaluation Software • www.edgewoodsolutions.com/partners/veritas.asp • Additional Resources • www.edgewoodsolutions.com/resources/links.asp © Edgewood Solutions 2005

  31. References • What’s New in 6.5.ppt – Veritas Corporation – Accessed 02.19.2004 • Edgewood Solutions.ppt – Veritas Corporation – Accessed 03.11.2004 • Gartner Research Note: P-15-2052 – Accessed 03.11.2004 • DISA-APM Overview.ppt – Veritas Corporation – Accessed 03.11.2004 • http://www.prnewswire.com/cgi-bin/stories.pl?ACCT=SVBIZINK3.story&STORY=/www/story/11-192003/0002061585&EDATE=WED+Nov+19+ 2003,+09:01+AM - Accessed 02.05.2004 © Edgewood Solutions 2005

  32. Summary and Thank You! • Performance tuning… • An on-going process for the entire life cycle • Difficult and time consuming • Requires people, processes and technology • Thank you! • Questions? © Edgewood Solutions 2005

More Related