Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning

1
Microsoft SQL Server Administration for
SAPPerformance Monitoring and Tuning
2
Overview

• SQL Server Architecture
• SQL Server with SAP
• Performance Monitoring and Tuning
• Administration and Troubleshooting
• Database Backup and Restore

3
Database Performance Analysis
4
Database Performance Analysis
5
Database Performance Analysis
6
Cache and CPU Tuning
?
Poor SQL statements?
Tune poor statements
Yes
No
2 CPU idle gt CPU busy?
Cache hit ratio gt 95?
Increase CPUs forSQL Server
Yes
Yes
No
No
OS paging?
All CPUs available for SQL Server?
No
No
Increase server main memory
Add CPU(s) to server
Yes
Yes
7
Database Configuration

• affinity mask
• awe enabled
• cost threshold for parallelism
• max degree of parallelism
• fillfactor
• index create memory
• lightweight pooling
• Locks / open objects / connections
• Max/Min server memory

8
Database Configuration

• max worker threads
• min memory per query
• network packet size / protocol
• priority boost
• query governor cost limit
• query wait
• recovery interval
• set working set size

9
Server Parameters
10
I/O System Tuning
?
Slow RAID identified?
Check all logical disks with NT perfmon
No
Yes
Avg. disk queue length gt 2 phys. Disks in RAID
RAID 5 and mostly write queue?
No
Decrease max async I/O
Yes
Yes
Switch to RAID 01
Peak I/O on I/O bus lt 133 MB/sec
Peak I/O on RAID lt 40 MB/sec
Yes
No
No
Yes
Yes
No
11
Server Configuration
Poor configuration
Hardware configuration
Poor database configuration
Main memory
Disk layout
Disks
CPU
Parameter settings
Cache hit ratio
Operating system paging
Disk response times
High I/O times
CPUutilization
gt95
SQL Server CPU utilization DB error log
Select 1 row via prim. key gt 10 ms
Page in gt 100 MB / h
Wait queue and low transfer rate
2 idle gt busy (total)
2 idle gt busy (SQL Server)
12
Dynamic Locking
Isolationlevel
Scan type (Range, Table, Probe)
Locking strategy(Table, Page, Row)
of rows in scan
of rows/page
Operation type (scan, update)
13
Multi-Granular Locking
T1 IX
T2 IS
Table

• To lock a fine granule must place intent locks
  at higher granules

T1 IX
T2 IS
Page
Page
Page
T1 X
T2 S
14
Lock Modes

• Standard multi-granular lock modes

Mode Description S Share - used for
reading X Exclusive - typically used for
writing U Update - used to evaluate prior to
writing IS Intent Share - share locking at finer
level IX Intent Exclusive - X locking at finer
level SIX Share Intent Exclusive
15
HOW does SQL lock ?

• How do you identify a lock?
• Lock resource
• Table Authors
• Page 23
• Row with Key 23812
• Lock manager knows nothing about resource
  format it simply does a memcmp()
• Lock resource format

Resource Type
Resource Specific Data
Database ID
16
Lock Resource Format

• Example resource formats
• Table
• Page
• RowID
• Key

325658
5
5
Object ID
2328
6
5
File Page
File Page Slot on Page
232811
9
5
Object ID IndexId 6byte Hash
32565822341186
7
5
Resourcetype
Database ID
17
New Lock Hints

• Granularity Hints
• ROWLOCK, PAGLOCK,TABLOCK
• ISOLEVEL Hints
• HOLDLOCK, NOLOCK
• READCOMMITTED, REPEATABLEREAD, SERIALIZABLE,
  READUNCOMMITTED All New

18
New Lock Hints

• READPAST
• Useful for implementing work queues
• UPDLOCK
• Select for update
• Deadlock due to select for update
• Starving lock waits
• LOCK_TIMEOUT(not a hint)
• Application response time

19
Concurrency issue analysis

• sp_who, sysprocesses
• sp_locks
• SQL Profiler
• sp_indexoption
• Deadlock
• DBCC inputbuffer
• Kill
• Lock waits
• Latches

20
Lockwait Situations
4. Work process
Update MARA
Requests MARA Lock
WAITING ...
3. Work process
Update MARA
Requests MARA Lock
Acquires MARA Lock
WAITING!
Working...
2. Work process
Update MARA
Requests MARA Lock
Acquires MARA Lock
WAITING!
Working...
Commit
1. Work process
Acquires MARA Lock
A long period of processing
Update MARA
Commit
Time
WP 1
WP 2
WP 3
Locked by
21
Monitoring Lockwaits
R/3 Lock Monitor
22
Process info from SQL Enterprise Manager
23
Locks from SQL Enterprise Manager
24
Locks from SQL Enterprise Manager
25
ST04 Detailed Analysis
26
Top Largest Tables
27
Top Largest Updated Tables
28
ST05
29
Table Analysis
30
Table Statistics
31
Selectivity
32
Density
33
DBCC Showcontig
34
Performance History
35
System Tables
36
System Procedures
37
Name Cache Stats
38
Stats on SPs
39
Stats on SPs
40
Stats on SPs
41
Stats on SPs
42
Stats on SPs
43
Query issues

• Unnecessary results
• Select with no where clause
• Select instead of selecting few columns
• Queries not qualified properly
• Poorly written queries
• Missing index
• Old statistics
• Incorrect optimization
• Index Tuning Wizard

44
Choosing index

• Space utilized by index
• Index maintenance overhead
• To cluster or not
• Bookmark lookup
• Range scan(OLTP Vs OLAP)
• Index columns
• Short with high selectivity
• Often used in many statements
• Covered
• Multi index query

45
Monitoring Query execution

• Analyze query execution plan
• Analyze the right plan - connection settings
• spid, blocked, waittype, cpu physical_io,
  memusage, open_tran from sysprocesses
• Execution plan, read, write, duration in SQL
  Profiler

46
Monitoring Query execution

• Stats I/o
• Stats time
• Write your own trace
• Expected time / threshold
• Worktable
• Scan Vs Seek

47
Query execution

• Query Hints
• Join, Index, Lock, Processing
• Distributed query execution
• TOP / SET ROWCOUNT
• FASTFIRSTROW
• IN / OR and subquery
• Selecting with alias

48
Using stored procedures

• Cached execution plan
• Recompiled when stats changes
• Warning Wrong Parameter to stored proc
• Binding parameter by position
• Set nocount
• sp_executesql
• Auto-parameterization in SQL7
• sp_recompile

49
Table statistics

• Auto update stats
• Rowmodctr and StatVersion
• Fullscan and sampling
• Auto column stats
• Explicit update stats
• Explicit column stats
• sp_autostats
• Queue / log table
• sp_recompile

50
Tuning Expensive SQL Statements
Poor statement
SQL Explain
Where used list
DDIC info
Is there asuitableindex?
GoodOptimizer decision?
Inefficientcoding?
Yes
Yes
No
No
Statistics page
Yes
Re-code
Index statistics up to date?
Autoupdate stats on?
Yes
Yes
Update statistics
No
No
Re-code or change index
Switch on auto updstats
51
Lab

• Improve the slow running report
• Use ST05
• Stats on SPs(ST04)
• And other methods

52
Summary

• Write module summary