Transcript Slide 1

SQL Server
Performance Tuning
Concepts
MDCFUG
September 13, 2005
Jeremy Kadlec of Edgewood Solutions
[email protected] | 410.591.4683
www.edgewoodsolutions.com
© Edgewood Solutions 2005
1
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
2
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
3
People, Process, Technology
Talented team with
architecture, hardware,
development and
database expertise
People
Process
Documented and
practiced processes
to analyze, prioritize,
develop, test,
implement and learn
Technology
Intuitive tools with the ability to make informed
decisions and accurate corrections efficiently
www.edgewoodsolutions.com
© Edgewood Solutions 2005
4
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
5
Performance Tuning Basics
Perf Tuning - Circle of Life
Learn
Analyze
Implement
Prioritize
Test
Develop
www.edgewoodsolutions.com
• 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
© Edgewood Solutions 2005
6
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’
www.edgewoodsolutions.com
© Edgewood Solutions 2005
7
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
8
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
9
Performance Tuning Continuum
SQL Server Performance Tuning Process Automation
Automated
Manual
Educated
Guess
People:
Process:
Technology:
Entire company
Reactive approach
No tools
www.edgewoodsolutions.com
Hunt and
Peck
Entire company
Reactive approach
Disjointed tools
© Edgewood Solutions 2005
Tool Set
Entire company
Proactive approach
Integrated tools
10
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
11
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
12
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
13
Enterprise Manager
Overview – Primary
tool to manage all SQL
Server functionality
across the enterprise
Features – Ability to
manage database
processes, locking,
indexes, configurations,
etc.
www.edgewoodsolutions.com
© Edgewood Solutions 2005
14
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
15
Performance Monitor Counters
•
Memory
•
Network Interface
•
–
–
–
–
Page
Page
Page
Page
Reads/sec
Writes/sec
Input/sec
Output/sec
–
–
–
–
–
Bytes Received/sec
Bytes Sent/sec
Bytes Total/sec
Current Bandwidth
Output Queue Length
•
Objects
•
Paging File
•
– All
– All
www.edgewoodsolutions.com
•
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
© Edgewood Solutions 2005
16
Performance Monitor Counters
• Server Work Queues
–
–
–
–
–
• SQLServer:Databases
Active Threads
Available Threads
Queue Length
Total Bytes/sec
Total Operations/sec
– Transactions/sec
• SQLServer:General
Statistics
• SQLServer:Access Methods
– Full Scans
– Page Splits/sec
– Table Lock Escalations/sec
– Logins/sec
– Logouts/sec
– User Connections
• SQLServer:Locks
– Number of Deadlocks/sec
• SQLServer:Cache Manager
– Cache Hit Ratio - _Total
– Cache Pages - _Total
www.edgewoodsolutions.com
© Edgewood Solutions 2005
17
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
18
SQL Profiler Event Data
• Cursors
– CursorOpen
– CursorExecute
– CursorClose
• Errors and Warnings
– Hash Warning
– Missing Column
Statistics
• Locks
– Lock:Deadlock
– Lock:Timeout
• TSQL
– Unprepare SQL
www.edgewoodsolutions.com
• 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
© Edgewood Solutions 2005
19
Query Analyzer – Query Plan
Overview – Ability to
graphically review the
query execution plan
www.edgewoodsolutions.com
© Edgewood Solutions 2005
20
SQL Server Objects
Object
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
21
Common SQL Server
Performance Problems
www.edgewoodsolutions.com
© Edgewood Solutions 2005
22
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
23
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
24
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
25
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
26
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
27
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
28
Edgewood Offerings
Customer centric
solutions with
beneficial results
Couple products
and services for a
complete solution
Training
Planning
Auditing
www.edgewoodsolutions.com
Integration
© Edgewood Solutions 2005
29
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
www.edgewoodsolutions.com
© Edgewood Solutions 2005
30
References
1.
2.
3.
4.
5.
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/cgibin/stories.pl?ACCT=SVBIZINK3.story&STORY=/www/story
/11-192003/0002061585&EDATE=WED+Nov+19+
2003,+09:01+AM - Accessed 02.05.2004
www.edgewoodsolutions.com
© Edgewood Solutions 2005
31
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 2005
32