SQL%20Server%20performance%20monitoring%20and%20tuning

1
SQL Server performance monitoring and tuning

• Buck Woody

2
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 Administrators Guide to SQL Server 2005

3
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

4
Phases of performance tuning

• Define components
• Evaluate objects
• Interpret findings
• Create an action plan

5
Performance tracking

• Use tracking tool of your choice
• Word
• Excel
• Database
• Methodology works on any platform

6
Define components

• A holistic view of the landscape
• Path determination
• Systems
• Software
• Hardware

7
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

8
Define components

• A holistic view of the landscape
• Path determination
• Systems
• Software
• Hardware

9
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.

10
Define components

• A holistic view of the landscape
• Path determination
• Systems
• Software
• Hardware

11
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

12
Define components

• A holistic view of the landscape
• Path determination
• Systems
• Software
• Hardware

13
The software

• Document software drivers, interfaces and code
• Only concerned with representative systems.
• Avoid making immediate changes if you change the
  test, you cant determine the exact issue.
• Do take care of security issues.
• WinMSD
• Graphical representation of your system

14
Define components

• A holistic view the landscape
• Path determination
• Systems
• Software
• Hardware

15
The hardware

• Document hardware
• Networking
• Memory
• Input/Output
• hard drives
• storage area networks (SANs)
• network-attached storage (NAS) devices

16
Evaluate objects

• Tools
• Working with a baseline
• Working without a baseline
• Dont fix anything yet!

17
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

18
Evaluate objects

• Tools
• Working with a baseline
• Working without a baseline
• Dont fix anything yet!

19
Gather a baseline

• Working with a baseline
• Collect data when the problem doesnt 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).

20
Evaluate objects

• Tools
• Working with a baseline
• Working without a baseline
• Dont fix anything yet!

21
Interpret findings

• Gather subject matter experts
• You cant do it all dont try
• Gather their thoughts
• Make everyone come up with what they think
• Agree on common interpretations
• Dont sweat the small stuff
• Table differences
• Dont fix anything yet!

22
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

23
Methodology review

• Gather component list
• Evaluate objects
• Interpret findings
• Create an action plan

24
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-server-perf
  ormance.com
• Administrators Guide to SQL Server 2005