www.edgewoodsolutions.com© edgewood solutions 20051 sql server performance tuning concepts mdcfug...

www.edgewoodsolutions.com © Edgewood Solutions 2005 1

SQL Server Performance

Tuning ConceptsMDCFUG

September 13, 2005

Jeremy Kadlec of Edgewood Solutions

[email protected] | 410.591.4683


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


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– [email protected]– 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


People, Process, Technology

Talented team with architecture, hardware, development and database expertise

Documented and practiced processes to analyze, prioritize, develop, test, implement and learn

Intuitive tools with the ability to make informed decisions and accurate corrections efficiently

People

Technology

Process


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


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

Analyze

Prioritize

Develop

Test

Implement

Learn

Perf Tuning - Circle of Life


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’


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


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


Performance Tuning Continuum

SQL Server Performance Tuning Process Automation

Educated Guess

Manual Automated

Hunt and Peck

Tool Set

Entire company

Reactive approach

No tools

Entire company

Reactive approach

Disjointed tools

Entire company

Proactive approach

Integrated tools

People:

Process:

Technology:


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


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


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


Enterprise Manager

Overview – Primary tool to manage all SQL Server functionality across the enterprise

Features – Ability to manage database processes, locking, indexes, configurations, etc.


Performance MonitorOverview – 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


Performance Monitor Counters

• 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

• 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


SQL Server ProfilerOverview – 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


SQL Profiler Event Data• 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


Query Analyzer – Query Plan

Overview – Ability to graphically review the query execution plan


SQL Server ObjectsObject Functionality

sp_who2 Current system processes

sysprocesses System table with processes

sp_lock Current locks by spid

sp_configure SQL Server configurations

fn_get_sql Snapshot of processes

fn_virtualfilestats I\O statistics for each database file

SHOWCONTIG Object fragmentation

Trace flags Capture additional information


Common SQL Server Performance Problems


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


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


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


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


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


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


Training

Auditing

Planning

Integration

Customer centric solutions with beneficial results

Couple products and services for

a complete solution

Edgewood Offerings


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


References1. What’s New in 6.5.ppt – Veritas Corporation –

Accessed 02.19.20042. Edgewood Solutions.ppt – Veritas Corporation –

Accessed 03.11.2004 3. Gartner Research Note: P-15-2052 – Accessed

03.11.20044. DISA-APM Overview.ppt – Veritas Corporation –

Accessed 03.11.2004 5. 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


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?

www.edgewoodsolutions.com© edgewood solutions 20051 sql server performance tuning concepts mdcfug...

Documents