Performance Monitoring for SQL

1
Performance Monitoring for SQL

• Paul Shearer ACS
• Bill Stevenson - ACS

2
Disclaimer

• With the exception of cited sources all
  statements expressed herein are the opinions of
  Paul and Bill arrived at over five years period
  of managing over a 100 SQL instances. These
  experiences are mainly based on OLTP systems and
  may not by directly applicable to your highly
  customized, data warehouse solution. Past
  performance does not guarantee future results,
  even though we will act like it does.

3
The BIG Five -- Bottlenecks

• Memory
• CPU
• Disk
• Network
• Application Design / Concurrency

4
Memory Bottlenecks

• This is perhaps the most important of the
  bottlenecks.
• SQL Server caches frequently accessed pages into
  memory.
• The more memory SQL has available for use the
  less it accesses disk.
• Disk access is over a 1000X slower than memory
  access!

5
Memory 32 bit

• A 32-bit memory space will allow the addressing
  up to 2 GB of ram.
• On a 4 GB server memory is spilt by default into
  two 32 bit address spaces OS and APP.
• If the Server is running Windows 2000 Advanced
  Server or Windows 2003 server the /3GB flag will
  allow 1 GB to be stolen from the OS space and
  given to the APP space.
• /3GB is EVIL! Stealing is wrong.

6
Memory 32 bit - continued

• The /PAE flag will allow for the addressing of up
  to 8 gigs of RAM in Windows 2000 Advanced Server
  and SQL Server 2000 Enterprise Edition.
• Windows 2003 Enterprise Server will allow for up
  to 32 GB to be used by the SQL server.

7
Memory 64 bit

• This is a flat address space that can address
  memory into the terabytes.
• Clients with VLDB this should be considered as an
  option.
• NOTE Clock speeds for 64-bit are slower than
  32-bit.

8
Win64 /
9
CPUs

• CPU is used heavily in Query Plan selection.
• Stores procedures have pre-compiled plans and
  thus less CPU impacts that if the code was ran
  from Query Analyzer.
• If the statistics have changed a lot since the
  plan was created the SP may be running with a bad
  plan.
• AD-hoc queries get a new plan every time.

10
CPUs Query Parallelism

• Query parallelism can create CPU based
  bottlenecks.
• A SQL wait type of CXPACKET indicates this
  problem exists.
• SP_CONFIGURE can be used to control the level of
  parallelism.

11
CPUs Compressed Backups

• SQLZIP / REDGATE / Lightspeed have been know on
  some systems to create a CPU bottleneck.
• For large databases consider only doing a weekly
  full backup and differentials backups during the
  week.
• Does not appear to be an issue on systems with
  CPUs 2 GHz and greater.

12
Detecting CPU Bottlenecks

• Permon
• Average CPU Utilization
• Average processor Queue Length
• System-gtProcesses-gtSQL Server
• SQL Waits
• CXPACKET

13
Disk Subsystem

• Any time data is accessed that is not in memory
  or a write operation occurs it will hit the
  disks.
• Any time your Average disk Queue Length is
  greater than (2 X of physical disks) this
  indicates a disk bottleneck.
• Average Disk Read Queue Length and Average Disk
  Write Queue Length are the two components that
  make up Average Disk Queue Length.

14
Disk Bottlenecks Filegroups

• Splitting out the data, specific tables, and
  indexes to separate file groups is a good
  strategy.
• SQL assigns a reader and writer for each file
  group. Thus the more file, the more parallelism.
• Moving the file to separate physicals disk is an
  even better strategy.

15
Disk I/O Bottlenecks Common Causes

• Bad Queries Large tables.
• A Select count() will cause a full table scan.
• A where clause lacking a good index will cause
  a full table scan.
• Index Maintenance.
• Fragmentation in both clustered and non-clustered
  indexes.
• Periodic rebuilding of a cluster index will
  restore the original fill factor.
• DBCC REINDEX is better than a defrag.

16
Detecting Disk Bottlenecks - Perfmon

• Average Disk Queue Length
• Average Disk Read Queue Length
• Average Disk Write Queue Length
• Disk Seconds per Read (4 to 8 ms)
• Disk Time

17
Detecting Disk Bottlenecks SQL Waits

• PAGEIOLATCH_x
• WRITELOG

18
Detecting Disk Bottleneck FILEIOSTATS
19
Network Bottlenecks Common Causes

• Often occurs during a backup operation.
• Dump files being pulled off to tape.
• Or dump files being spooled to another server.
• Reading very large tables

20
Detecting Network Bottlenecks

• Perfmon
• Packets sent
• Packets received
• Packets error
• SQL Wait Types
• NETWORKIO

21
Application Design / Concurrency

• Lock Types (simplified)
• SHARED
• Allows others to READ the locked data
• Blocks others to UPDATING or DELETING data
• EXCLUSIVE
• Mine! Do not touch.!
• Hint (the NOLOCK clause will over the lock)

22
Application Design

• A simple SELECT statement will create a shared
  lock.
• If you select a large table no one will be able
  to update or delete from it until your select is
  completed.
• Avoid use of cursors.
• Your goal should be to hold a lock for the
  shortest period of time possible.

23
Simple Solution to the problem..

• Upgrade to SQL 2005!!!!
• New isolation level creates a shadow copy of
  the data and prevents locking.
• Only Writers block writers.

24
Tools of the Trade Perfmon Counters

• CPU
• Average processor Utilization
• Average processor Queue Length
• Process - gt SQLSERVER
• Disks
• Average Disk Queue Length
• Average Disk Read Queue Length
• Average Disk Write Queue Length
• Disk Seconds per Read.
• Disk Time

• Memory
• MB Free
• Network
• Packets received
• Packets sent
• Packets error
• SQL Server
• Buffer-Hit ratio
• SQL Buffer Mgr Page Life Expectancy
• Transaction per second

25
Tools of the Trade SQL Waits

• CPU
• CXPACKET
• Disk
• PageIOLatch_x
• Writelog

• Memory
• Latch
• PatchIOLatch_SH
• Network IO
• NetworkIO
• Application Design
• Lock_x

26
Tools of the TradeFile IO Stats