Transcript SQL Server Performance Tuning

SQL Server performance
monitoring and tuning
Buck Woody
About our speaker







Buck Woody,
Working with technology since 1979.
Technical writer, DBA, technician, help desk, manager and
consultant.
President of the Tampa SQL Server User Group
Microsoft Certified Professional, Microsoft Certified
Systems Engineer and Microsoft Certified Database
Administrator
Experience with SQL Server, Oracle, MySQL,
SQLPostgre, DB2
Author Administrator’s Guide to SQL Server 2005
Make SQL Server faster

Methodology
Look at SQL Server from a holistic standpoint.
 How to baseline a SQL Server system.
 How to track it from a “landscape perspective.”


Evaluate the system now and going forward
Phases of performance tuning
 Define
components
 Evaluate objects
 Interpret findings
 Create an action plan
Performance tracking

Use tracking tool of your choice
Word
 Excel
 Database


Methodology works on any platform
Define components

A holistic view of the landscape
 Path
determination
 Systems
 Software
 Hardware
The landscape

“Literally everything”
Server itself
 Clustering components, if clustered
 Networking, cards and driver levels
 Routers and switches
 Client workstations
 Etc.


An entire representation of your environment
Define components

A holistic view of the landscape
 Path determination
 Systems
 Software
 Hardware
The path


Determine how data gets from a fairly unique
client machine to the server.
Diagram the path:
Paint
 PowerPoint
 Visio
 Network tools


Determine areas of slowdown.
Define components

A holistic view of the landscape
 Path determination
 Systems
 Software
 Hardware
The system

Document the architecture
Two tier – client and a server
 Three tier – client, middle layer and a server
 N tier – multiple systems
 SOA – lots of moving parts

Define components

A holistic view of the landscape
 Path determination
 Systems
 Software
 Hardware
The software

Document software drivers, interfaces and code
Only concerned with representative systems.
 Avoid making immediate changes; if you change the
test, you can’t determine the exact issue.
 Do take care of security issues.


WinMSD

Graphical representation of your system
Define components

A holistic view – the landscape
 Path determination
 Systems
 Software
 Hardware
The hardware

Document hardware
Networking
 Memory
 Input/Output

hard drives
 storage area networks (SANs)
 network-attached storage (NAS) devices

Evaluate objects




Tools
Working with a baseline
Working without a baseline
Don’t fix anything yet!
The tools



Tools
 SQL Server built-in tools
 System and Performance Monitor
 Third-party tools
Collect objects and counters
 Memory
 CPU
 Network
 I/O
Store data
Evaluate objects




Tools
Working with a baseline
Working without a baseline
Don’t fix anything yet!
Gather a baseline

Working with a baseline
Collect data when the problem doesn’t exist.
 Gather a lot of detail.


Working without a baseline
Start broad and zero in on problems.
 Look at wider counters (i.e. CPU performance).

Evaluate objects




Tools
Working with a baseline
Working without a baseline
Don’t fix anything yet!
Interpret findings

Gather subject matter experts


Gather their thoughts



Make everyone come up with what they think
Agree on common interpretations


You can’t do it all – don’t try
Don’t sweat the small stuff
Table differences
Don’t fix anything yet!
Create an action plan







Decide on the fixes
Decide who should implement
Decide risks and rewards
Detail timelines
Create backup plan
Implement
Monitor for change, report
Methodology review
1.
2.
3.
4.
Gather component list
Evaluate objects
Interpret findings
Create an action plan
Resources

SearchSQLServer.com Performance and Tuning:
http://searchSQLServer.com/r/0,,59918,00.htm?
InformIT.com: http://www.informit.com
(Click on Reference Guides, then SQL Server)


SQL-Server-Performance.com: http://sql-serverperformance.com

Administrator’s Guide to SQL Server 2005