Managing SQL Server Performance

1
Managing SQL ServerPerformance

• AtlantaMDF
• August 11, 2003
• By Larry Ansley

2
(No Transcript)
3
Performance Maximization PlanIn Only 4 Easy Steps

• Denormalize
• Index Everything
• Use Full-Text Searches Liberally
• Get a Bigger, More Powerful System

4
Performance AreasHardware

• Memory
• CPU
• Storage
• Network

5
Performance AreasOperating System

• Configuration
• Version Upgrades

6
Performance AreasOperating System

• Configuration
• File Management
• Version Upgrades

7
Performance AreasSQL Server

• Configuration Options
• File Management
• Version Upgrades

8
Performance AreasDatabases

• Configuration Options
• Tables
• Indexes
• File Management

9
Performance AreasStored Procedures

• Logic
• Best Practices

10
Performance Killers

1. Poor indexing
2. Inaccurate statistics
3. Excessive fragmentation
4. Non-reusable execution plans
5. Frequent recompilation
6. Excessive blocking and deadlocks
7. Poor query design

11
Performance Killers

1. Poor database design
2. Improper cursor use
3. Ineffective connection pooling
4. Improper database log configuration

12
Taming The Performance Killers

• Planning
• ER Diagrams
• Data Modeling
• Capacity Requirements
• Procedure Case Analysis
• Follow Best Practices in Development

13
Taming The Performance Killers

• Good Record Keeping
• Logs
• Baselines
• Audits

14
Performance BaselinesBenefits

• Allows us to analyze resource bottlenecks
• Allows us to troubleshoot by comparing system
  utilization patterns with pre-established
  baselines
• Allows us to make accurate estimates in capacity
  planning and scheduling hardware upgrades

15
Performance BaselinesBenefits

• Aids us in identifying low utilization periods,
  when we can execute administrative activities
• Helps us distinguish between performance
  perception and reality

16
Performance BaselinesBaseline Tools

• System Monitor (Key Resource Counters)
• CPU Processor Time (85)
• CPU Processor Queue Length (2)
• Memory Available Bytes (4 MB)
• Memory Pages / Sec (20)
• Memory Buffer Cache Hit Ratio ( 90)

17
Performance BaselinesBaseline Tools

• System Monitor (Key Resource Counters)
• Disk Disk Time (90)
• Disk Avg. Disk Queue Length (number of spindles
  2)
• Network Net Utilization (30 for Ethernet)
• SQL Server User Connections

18
Performance BaselinesBaseline Tools

• SQL Profiler
• Record Traces for Major Processes
• Duration, CPU, Reads, Writes

19
Performance Audit

• System Monitor
• Hardware
• Operating System
• SQL Server Configuration
• Database Settings
• Indexes
• Application and Transact-SQL

20
Performance AuditSystem Monitor

• Processor Processor Time
• Recurring period exceeding 80 indicate CPU
  bottlenecks
• System Processor Queue Length
• Recurring periods exceeding 2 per CPU indicate
  CPU bottlenecks
• Consider Maximum Worker Threads setting

21
Performance AuditSystem Monitor

• Memory Pages / Sec
• Average around 0 over 24 hours
• Average over 20 indicates memory bottleneck
• Memory Available Bytes
• Should be greater than 5 MB
• Dedicated SQL Servers attempt to maintain 4-10 MB
  free
• Less than 5 MB indicates a memory bottleneck

22
Performance AuditSystem Monitor

• SQL Server Buffer Buffer Cache Hit Ratio
• For OLTP application, should exceed 90, ideally
  around 99
• OLAP (Analysis Services) application work well
  with lower ratios
• SQL Server General User Connections
• If this exceeds 255, Maximum Worker Threads
  setting should be increased to remain greater
  than user connections, thus avoiding thread
  sharing.

23
Performance AuditSystem Monitor

• Physical Disk Disk Time
• Should run less than 55
• Recurring periods (10 minutes or more) exceeding
  55 indicate I/O bottlenecks
• Physical Disk Avg. Disk Queue Length
• Should run less than 2 per spindle
• Recurring periods exceeding 2 indicate I/O
  bottlenecks

24
Performance AuditHardware

• Number of CPUs
• CPU MHz
• CPU L2 Cache Size
• Physical Ram Amount
• Total Amount of Available Drive Space on Server
• Total Number of Physical Drives in Each Array

25
Performance AuditHardware

• Hardware vs. Software Raid
• RAID Level of Each Array Used
• Disk Fragmentation Level
• Location of Operating System
• Location of SQL Server Executables
• Location of Swap File
• Location of tempdb Database

26
Performance AuditHardware

• Location of System Databases
• Location of User Databases
• Location of Log Files
• Number of Disk Controllers in Server
• Type of Disk Controllers in Server
• Size of Cache in Disk Controllers In Server
• Is Write Cache in Disk Controller On or Off

27
Performance AuditHardware

• Speed of Disk Drives
• How Many Network Cards Are in Server
• Are the Network Cards Hard-Coded for Speed/Duplex
• Are the Network Cards Attached to a Switch
• Are All the Hardware Drivers Up-to-Date
• Is This Physical Server Dedicated to SQL Server

28
Performance AuditOperating System

• Are the Disk Partitions NTFS 5.0
• Is NTFS Data File Encryption and Compression
  Turned Off
• Is the Server Updated With the Latest Service
  Pack
• Does the Server Have the Most Current,
  Microsoft-Certified Hardware Drivers

29
Performance AuditOperating System

• Is the Windows 2000 Server Configured as a
  Stand-Alone Server
• Are the Physical Files on the Server Overly
  Fragmented
• Is the Application Response Setting Set to
  Optimize Performance for Background Services
• Has Security Auditing Been Turned On

30
Performance AuditOperating System

• How Large Is the Servers PAGEFILE.SYS swap file
• Have Unnecessary Services Been Turned Off
• Have All Unnecessary Network Protocols Been
  Turned Off

31
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Affinity mask Yes Yes 0
Awe enabled Yes Yes 0
Cost threshold for parallelism Yes No 5
Cursor threshold Yes No -1
Fill factor () Yes Yes 0
32
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Index create memory (KB) Yes No 0
Lightweight pooling Yes Yes 0
Locks Yes Yes 0
Max degree of parallelism Yes No 0
33
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Max server memory (MB) Yes No 2,147,483,647
Max text repl size (B) No No 65536
Max worker threads Yes Yes 255
Min memory per query (KB) Yes No 1024
34
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Min server memory (MB) Yes No 0
Nested triggers No No 1
Network packet size (B) Yes No 4096
Open objects Yes Yes 0
35
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Priority boost Yes Yes 0
Query governor cost limit Yes No 0
Query wait (s) Yes No -1
Recovery interval (min) Yes No 0
36
Performance AuditSQL Server Configuration
Settings Advanced Restart Default Current
Scan for startup procs Yes No 0
Set working set size Yes Yes 0
User connections Yes No 0
37
Performance AuditDatabase Configuration Settings
Database Setting Default Current
Auto_close Off
Auto_create_statistics On
Auto_update_statistics On
Auto_shrink Off
Read_only Off
Torn_page_detection On in 2000 Off in 7.0
38
Performance AuditDatabase Configuration Settings
Database Setting Default Current
Compatibility level 80 for 2000 70 for 7.0
Database auto grow On
Transaction log auto grow On
39
Performance AuditIndexes

• Have you run the Index Tuning Wizard recently
• Does every table in each database have a
  clustered index
• Are any of the columns in any table indexed more
  than once
• Are there any indexes that are not being used in
  queries

40
Performance AuditIndexes

• Are the indexes too wide
• Do the tables that are joined have the
  appropriate indexes on the joined columns
• Are the indexes unique enough to be useful
• Are you taking advantage of covering indexes
• How often are indexes rebuilt
• What is your index fillfactor

41
Performance AuditTransact-SQL

• Does the Transaction-SQL code return more data
  than needed
• Are cursors being used when they dont need to be
• Are Union and Union Select properly used
• Is Select Distinct being used properly
• Is the Where clause Sargable
• Are temp tables used when they arent needed

42
Performance AuditTransact-SQL

• Are hints being properly used in queries
• Are view unnecessarily being used
• Are stored procedures being used whenever
  possible
• Inside stored procedures, is Set NoCount On being
  used
• Do any of your stored procedures start with sp_

43
Performance AuditTransact-SQL

• Are all stored procedures owned by DBO and
  referred to in the form of databaseowner.objectnam
  e
• Are you using constraints or triggers for
  referential integrity
• Are transactions being kept as short as possible

44
Performance AuditApplications

• Is the application using stored procedures,
  strings of T-SQL code, or using an object model,
  like ADO, to communicate with SQL Server
• What method is the application using to
  communicate with SQL Server DB-Lib, DAO, RDO,
  ADO, .Net
• Is the application using ODBC or OLE DB to
  communicate with SQL Server

45
Performance AuditApplications

• Is the application taking advantage of connection
  pooling
• Is the application properly opening, reusing, and
  closing connections
• Is the T-SQL code being sent to SQL Server
  optimized for SQL Server, or is it generic SQL
• Does the application return unnecessary data

46
Performance AuditApplications

• Is the application taking advantage of connection
  pooling
• Is the application properly opening, reusing, and
  closing connections
• Is the T-SQL code being sent to SQL Server
  optimized for SQL Server, or is it generic SQL
• Does the application return unnecessary data

47
Performance AuditApplications

• Does the application keep transaction open when
  the user is modifying data

48
Monitoring Measuring Tools

• Enterprise Manager
• Query Analyzer
• System Monitor
• SQL Profiler
• 3rd Party

49
Monitoring Measuring ToolsEnterprise Manager

• Management / Current Activity
• Process Info (sp_who2)
• Locks / Process ID (sp_lock)
• Locks / Object (sp_lock)
• Management / SQL Server Logs

50
Monitoring Measuring ToolsQuery Analyzer

• sp_who / sp_who2
• sp_lock
• sp_spaceused
• sp_monitor
• Showplan_Text
• Showplan_All
• Set Statistics IO
• Set Statistics Time
• Set Statistics Profile

51
Monitoring Measuring ToolsQuery Analyzer

• DBCC Commands
• DBCC UserOptions
• DBCC Show_Statistics
• DBCC ShowContig
• DBCC IndexDefrag
• DBCC PerfMon
• DBCC TraceOn / DBCC TraceOff

52
Monitoring Measuring ToolsQuery Analyzer

• Built-in Functions
• _at__at_CPU_Busy
• _at__at_IO_Busy
• _at__at_Packet_Errors
• _at__at_Connections
• _at__at_Total_Read
• _at__at_Total_Write
• _at__at_Total_Errors
• _at__at_TranCount

53
Monitoring Measuring ToolsQuery Analyzer

• System Tables
• Master.dbo.sysprocesses
• Master.dbo.syslockinfo
• Master.dbo.sysperfinfo
• Master.dbo.syscacheobjects
• dbo.sysobjects
• dbo.sysindexes
• _WA_Sys_AcctNo_2216C367, hind_1400444113_1A_6A_7A
• dbo.sysindexkeys
• dbo.syscolumns
• dbo.sysdepends

54
SQL Profiler Query Analyzer Highest Execution
Count
55
SQL Profiler Query Analyzer Longest Single
Duration
56
SQL Profiler Query Analyzer Longest Aggregate
Duration
57
SQL Profiler Query Analyzer Recompiles
58
Suggested ReadingPrinted Books

• Inside SQL Server 2000
• Kalen Delaney (Microsoft Press, 2001)
• SQL Server Query Performance Tuning Distilled
• Sajal Dam (Curlingstone, 2003)
• SQL Server 2000 for Experienced DBAs
• Brian Knight (Osborne, 2003)

59
Suggested ReadingE-Books

• Microsoft SQL Server Books On Line
• Start to Finish Guide to SQL Server Performance
  Monitoring
• Brian Kelly (NetImpress, 2002)
• Hands-On SQL Server 2000 Troubleshooting Locking
  and Blocking
• Kalen Delaney (NetImpress, 2003)

60
Suggested ReadingPeriodicals

• SQL Server Magazine (Penton Media)

61
Suggested ReadingWeb Sites Web Articles

• http//www.sql-server-performance.com
• Brad McGehee How to Perform a SQL Server
  Performance Audit and many others
• Randy Dyess An Introduction to SQL Server Query
  Tuning and many others
• Joe Chang SQL Server Quantitative Performance
  Analysis
• http//www.sqlservercentral.com
• Brian Knight

62
Suggested ReadingWeb Sites Web Articles

• http//www.sqlmag.com
• Kalen Delaney Recompiling Riddles and many
  more
• Neil Pike What is the precedence of the SET
  commands, database options, session options, etc.
• http//www.sqlteam.com
• http//www.algonet.se/sommar/index.html
• Erland Sommarskog MBP aba_lockinfo

63
Suggested ReadingWeb Sites Web Articles

• http//www.msdn.microsoft.com
• http//support.microsoft.com/default.aspx?scidfh
  EN-USKBHOWTO
• http//www.msdn.microsoft.com/library/default.asp?
  url/nhp/Default.asp?contentid28000409
• http//www.microsoft.com/sql/