Transcript SQL Server “Sphinx”

Microsoft SQL Server
Administration for SAP
Performance Monitoring and
Tuning
Overview





SQL Server Architecture
SQL Server with SAP
Performance Monitoring and Tuning
Administration and Troubleshooting
Database Backup and Restore
2
Database Performance
Analysis
3
Database Performance
Analysis
4
Database Performance
Analysis
5
Cache and
CPU
Tuning
?
Poor SQL
statements?
Yes
Tune poor
statements
No
Cache hit ratio
> 95%?
No
No
Increase
server main
memory
OS paging?
Yes
Yes
Yes
+
2 * CPU idle
> CPU busy?
Increase
CPUs for
SQL Server
No
All CPUs available
for SQL Server?
Yes
No
Add CPU(s)
to server
6
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
7
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
8
Server Parameters
9
I/O
System
Tuning
?
Slow RAID
identified?
Check all
logical disks
with NT perfmon
No
Yes
Decrease
max async I/O
Avg. disk queue
length > 2 * phys.
Disks in RAID
Yes
Yes
No
Peak I/O
on I/O bus
< 133 MB/sec
No
Yes
Add / speed up I/O bus
No
RAID 5
and mostly write
queue?
Peak I/O
on RAID
< 40 MB/sec
Yes
Add RAID
+ move disks
Yes
Switch to
RAID 0+1
No
Check disk
+ controller
10
Server Configuration
Poor configuration
Hardware
configuration
Poor database
configuration
Disk
layout
Cache
hit ratio
>95%
Parameter
settings
2 * idle > busy
(SQL Server)
Disks
CPU
High I/O
times
SQL Server
CPU utilization
DB error log
Main
memory
Operating
system
paging
CPU
utilization
Disk
response
times
Select 1 row
Page in
Wait queue
via prim. key
>
100
MB
/
h
2 * idle > busy and low
> 10 ms
(total)
transfer rate
11
Dynamic Locking
Isolation
level
Scan type
(Range, Table,
Probe)
# of rows
in scan
Locking strategy
(Table, Page, Row)
# of rows/page
Operation type
(scan, update)
12
Multi-Granular Locking
T2: IS
T1: IX

Table
T2: IS
Page
Row
T1: IX
Page
Row
Page
Row
To lock a fine granule
must
place intent locks
at higher granules
T1: X
T2: S
13
Lock Modes

Standard multi-granular
lock modes
Mode
S
X
U
IS
IX
SIX
Description
Share - used for reading
Exclusive - typically used for writing
Update - used to evaluate prior to writing
Intent Share - share locking at finer level
Intent Exclusive - X locking at finer level
Share Intent Exclusive
14
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 Database ID
Resource Specific Data
15
Lock Resource Format

Example resource formats:

Table:
5

5 2:328:11
File#: Page#
File# : Page# :
Slot on Page
Key:
7
Resource
type
5 2:328
RowID:
9

Object ID
Page:
6

5 325658
5 325658:2:2341186 Object ID : IndexId :
6byte Hash
Database ID
16
New Lock Hints

Granularity Hints


ROWLOCK, PAGLOCK,TABLOCK
ISOLEVEL Hints


HOLDLOCK, NOLOCK
READCOMMITTED,
REPEATABLEREAD, SERIALIZABLE,
READUNCOMMITTED [All New]
17
New Lock Hints

READPAST


UPDLOCK




Useful for implementing work queues
Select for update
Deadlock due to select for update
Starving lock waits
LOCK_TIMEOUT(not a hint)

Application response time
18
Concurrency issue analysis









sp_who, sysprocesses
sp_locks
SQL Profiler
sp_indexoption
Deadlock
DBCC inputbuffer
Kill
Lock waits
Latches
19
Lockwait Situations
4. Work
process
Update Requests
MARA MARA Lock
3. Work
process
2. Work
process
Update Requests
MARA MARA Lock
WAITING ...
Update Requests WAITING! Acquires
MARA MARA Lock
MARA Lock
1. Work Update
process MARA
Acquires
MARA Lock
Acquires
Working...
MARA Lock
WAITING!
Working... Commit
A long period
Commit
of processing
Time
Locked
MARA
by:
WP 1
WP 2
WP 3
20
Monitoring Lockwaits
R/3 Lock Monitor
21
Process info from SQL
Enterprise Manager
22
Locks from SQL Enterprise
Manager
23
Locks from SQL Enterprise
Manager
24
ST04 Detailed Analysis
25
Top Largest Tables
26
Top Largest Updated Tables
27
ST05
28
Table Analysis
29
Table Statistics
30
Selectivity
31
Density
32
DBCC Showcontig
33
Performance History
34
System Tables
35
System Procedures
36
Name Cache Stats
37
Stats on SPs
38
Stats on SPs
39
Stats on SPs
40
Stats on SPs
41
Stats on SPs
42
Query issues

Unnecessary results




Poorly written queries




Select with no where clause
Select * instead of selecting few
columns
Queries not qualified properly
Missing index
Old statistics
Incorrect optimization
Index Tuning Wizard
43
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
44
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
45
Monitoring Query execution



Stats I/o
Stats time
Write your own trace



Expected time / threshold
Worktable
Scan Vs Seek
46
Query execution

Query Hints






Join, Index, Lock, Processing
Distributed query execution
TOP / SET ROWCOUNT
FASTFIRSTROW
IN / OR and subquery
Selecting with alias
47
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
48
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
49
Tuning Expensive SQL
Statements
Poor
statement
SQL
Explain
DDIC
info
No
Is there a
suitable
index?
Where
used list
Good
Optimizer
decision?
Yes
Yes
Inefficient
coding?
No
Statistics
page
Update
statistics
Yes
Autoupdate
stats on?
No
Switch on
auto updstats
No
Yes
Index
statistics
up to
date?
Yes
Re-code
Re-code
or
change index
50
Lab

Improve the slow running report



Use ST05
Stats on SPs(ST04)
And other methods
51
Summary

Write module summary
52