microsoft sql server administration for sap performance monitoring and tuning
Post on 22-Dec-2015
240 views
TRANSCRIPT
![Page 1: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/1.jpg)
Microsoft SQL Server Microsoft SQL Server Administration for SAPAdministration for SAP
Performance Monitoring and Performance Monitoring and TuningTuning
![Page 2: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/2.jpg)
2
OverviewOverview
SQL Server ArchitectureSQL Server Architecture SQL Server with SAPSQL Server with SAP Performance Monitoring and TuningPerformance Monitoring and Tuning Administration and TroubleshootingAdministration and Troubleshooting Database Backup and RestoreDatabase Backup and Restore
![Page 3: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/3.jpg)
3
Database Performance Database Performance AnalysisAnalysis
![Page 4: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/4.jpg)
4
Database Performance Database Performance AnalysisAnalysis
![Page 5: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/5.jpg)
5
Database Performance Database Performance AnalysisAnalysis
![Page 6: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/6.jpg)
6
Cache and Cache and CPU CPU
TuningTuning Poor SQL statements?Poor SQL
statements?
Cache hit ratio> 95%?
Cache hit ratio> 95%?
2 * CPU idle> CPU busy?2 * CPU idle
> CPU busy?
OS paging?OS paging? All CPUs availablefor SQL Server?
All CPUs availablefor SQL Server?
Tune poorstatementsTune poorstatements
Increase server main
memory
Increase server main
memory
Add CPU(s)to server
Add CPU(s)to server
IncreaseCPUs for
SQL Server
IncreaseCPUs for
SQL Server+
Yes Yes
YesYes
No No
NoNo
?
Yes
No
![Page 7: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/7.jpg)
7
Database ConfigurationDatabase Configuration
affinity maskaffinity mask awe enabledawe enabled cost threshold for parallelismcost threshold for parallelism max degree of parallelismmax degree of parallelism fillfactorfillfactor index create memoryindex create memory lightweight poolinglightweight pooling Locks / open objects / connectionsLocks / open objects / connections Max/Min server memoryMax/Min server memory
![Page 8: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/8.jpg)
8
Database ConfigurationDatabase Configuration
max worker threadsmax worker threads min memory per querymin memory per query network packet size / protocolnetwork packet size / protocol priority boostpriority boost query governor cost limitquery governor cost limit query waitquery wait recovery intervalrecovery interval set working set sizeset working set size
![Page 9: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/9.jpg)
9
Server ParametersServer Parameters
![Page 10: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/10.jpg)
10
I/O I/O System System TuningTuning Slow RAID
identified?Slow RAIDidentified?
Avg. disk queuelength > 2 * phys.
Disks in RAID
Avg. disk queuelength > 2 * phys.
Disks in RAID
RAID 5 and mostly write
queue?
RAID 5 and mostly write
queue?
Check alllogical disks
with NT perfmon
Check alllogical disks
with NT perfmon
Switch to RAID 0+1Switch to RAID 0+1
Decreasemax async I/O
Decreasemax async I/O
Yes
No
No
?
No
Yes
Yes
Yes Peak I/O on I/O bus
< 133 MB/sec
Peak I/O on I/O bus
< 133 MB/sec
Add / speed up I/O bus
Peak I/O on RAID
< 40 MB/sec
Peak I/O on RAID
< 40 MB/sec
No
Yes
Add RAID + move disks
Yes
Check disk+ controller
No
![Page 11: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/11.jpg)
11
Server ConfigurationServer Configuration
Disk Disk layoutlayoutDisk Disk
layoutlayout
Parameter Parameter settingssettings
Parameter Parameter settingssettings
Hardware Hardware configurationconfiguration
Hardware Hardware configurationconfiguration
Main Main memorymemory
Main Main memorymemory
CPUCPUCPUCPU
DisksDisksDisksDisks
Poor database Poor database configurationconfiguration
Poor database Poor database configurationconfiguration
Poor configurationPoor configurationPoor configurationPoor configuration
Cache hit ratioCache hit ratio
SQL Server CPU utilization
DB error log
SQL Server CPU utilization
DB error log
High I/O times
High I/O times
Operating system paging
Operating system paging CPU
utilization CPU
utilization
Disk response
times
Disk response
times>95%
2 * idle > busy(SQL Server)
Select 1 row via prim. key> 10 ms 2 * idle > busy
(total)
Wait queueand low
transfer rate
Page in> 100 MB / h
![Page 12: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/12.jpg)
12
IsolationIsolationlevellevel
Scan type Scan type (Range, Table, (Range, Table,
Probe)Probe)
# of rows# of rowsin scanin scan
# of rows/page# of rows/page
Locking strategyLocking strategy(Table, Page, Row)(Table, Page, Row)
Operation type Operation type (scan, update)(scan, update)
Dynamic LockingDynamic Locking
![Page 13: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/13.jpg)
13
To lock a fine To lock a fine granule must granule must place intent locks place intent locks at higher granulesat higher granules
T1: IXT1: IX
T1: IXT1: IX
T1: XT1: X
T2: IST2: IS
T2: IST2: IS
T2: ST2: S
TableTable
PagePage PagePage PagePage
RowRow RowRow RowRow
Multi-Granular LockingMulti-Granular Locking
![Page 14: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/14.jpg)
14
ModeMode DescriptionDescription
SS Share - used for readingShare - used for reading
XX Exclusive - typically used for writingExclusive - typically used for writing
UU Update - used to evaluate prior to writingUpdate - used to evaluate prior to writing
ISIS Intent Share - share locking at finer levelIntent Share - share locking at finer level
IXIX Intent Exclusive - X locking at finer levelIntent Exclusive - X locking at finer level
SIXSIX Share Intent ExclusiveShare Intent Exclusive
Lock ModesLock Modes
Standard multi-granular Standard multi-granular lock modeslock modes
![Page 15: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/15.jpg)
15
Resource TypeResource Type Resource Specific DataResource Specific DataDatabase IDDatabase ID
HOW does SQL lock ?HOW does SQL lock ?
How do you identify a lock?How do you identify a lock? Lock “resource”Lock “resource”
Table “Authors”Table “Authors” Page 23Page 23 Row with Key = “23812”Row with Key = “23812”
Lock manager knows nothing Lock manager knows nothing about resource format; it simply about resource format; it simply does a “memcmp()”does a “memcmp()”
Lock resource format:Lock resource format:
![Page 16: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/16.jpg)
16
Example resource formats:Example resource formats: Table:Table:
Page:Page:
RowID:RowID:
Key:Key:
32565832565855 Object IDObject ID
2:3282:32866 File#: Page#File#: Page#
2:328:112:328:1199File# : Page# : File# : Page# : Slot on PageSlot on Page
325658:2:2341186325658:2:234118677 Object ID : IndexId : Object ID : IndexId : 6byte Hash6byte Hash
55
55
55
55ResourceResourcetypetype Database IDDatabase ID
Lock Resource FormatLock Resource Format
![Page 17: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/17.jpg)
17
New Lock HintsNew Lock Hints
Granularity HintsGranularity Hints ROWLOCK, PAGLOCK,TABLOCKROWLOCK, PAGLOCK,TABLOCK
ISOLEVEL HintsISOLEVEL Hints HOLDLOCK, NOLOCKHOLDLOCK, NOLOCK READCOMMITTED, READCOMMITTED,
REPEATABLEREAD, SERIALIZABLE, REPEATABLEREAD, SERIALIZABLE, READUNCOMMITTED [All New]READUNCOMMITTED [All New]
![Page 18: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/18.jpg)
18
New Lock HintsNew Lock Hints
READPASTREADPAST Useful for implementing work queuesUseful for implementing work queues
UPDLOCKUPDLOCK Select for updateSelect for update Deadlock due to select for updateDeadlock due to select for update Starving lock waitsStarving lock waits
LOCK_TIMEOUT(not a hint)LOCK_TIMEOUT(not a hint) Application response timeApplication response time
![Page 19: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/19.jpg)
19
Concurrency issue analysisConcurrency issue analysis
sp_who, sysprocessessp_who, sysprocesses sp_lockssp_locks SQL ProfilerSQL Profiler sp_indexoptionsp_indexoption DeadlockDeadlock DBCC inputbufferDBCC inputbuffer KillKill Lock waitsLock waits LatchesLatches
![Page 20: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/20.jpg)
20
Time
1. Work process
2. Work process
3. Work process
4. Work process
AcquiresMARA Lock
A long period of processing
CommitUpdate MARA
Working...WAITING!Update MARA
RequestsMARA Lock
AcquiresMARA Lock
Commit
Working...WAITING!Update MARA
RequestsMARA Lock
AcquiresMARA Lock
WAITING ...Update MARA
RequestsMARA Lock
MARAMARALocked
by:
WP 1 WP 2 WP 3
Lockwait SituationsLockwait Situations
![Page 21: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/21.jpg)
21
Monitoring LockwaitsMonitoring Lockwaits
R/3 Lock Monitor
![Page 22: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/22.jpg)
22
Process info from SQL Process info from SQL Enterprise ManagerEnterprise Manager
![Page 23: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/23.jpg)
23
Locks from SQL Enterprise Locks from SQL Enterprise ManagerManager
![Page 24: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/24.jpg)
24
Locks from SQL Enterprise Locks from SQL Enterprise ManagerManager
![Page 25: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/25.jpg)
25
ST04 Detailed AnalysisST04 Detailed Analysis
![Page 26: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/26.jpg)
26
Top Largest TablesTop Largest Tables
![Page 27: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/27.jpg)
27
Top Largest Updated TablesTop Largest Updated Tables
![Page 28: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/28.jpg)
28
ST05ST05
![Page 29: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/29.jpg)
29
Table AnalysisTable Analysis
![Page 30: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/30.jpg)
30
Table StatisticsTable Statistics
![Page 31: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/31.jpg)
31
SelectivitySelectivity
![Page 32: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/32.jpg)
32
DensityDensity
![Page 33: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/33.jpg)
33
DBCC ShowcontigDBCC Showcontig
![Page 34: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/34.jpg)
34
Performance HistoryPerformance History
![Page 35: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/35.jpg)
35
System TablesSystem Tables
![Page 36: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/36.jpg)
36
System ProceduresSystem Procedures
![Page 37: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/37.jpg)
37
Name Cache StatsName Cache Stats
![Page 38: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/38.jpg)
38
Stats on SPsStats on SPs
![Page 39: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/39.jpg)
39
Stats on SPsStats on SPs
![Page 40: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/40.jpg)
40
Stats on SPsStats on SPs
![Page 41: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/41.jpg)
41
Stats on SPsStats on SPs
![Page 42: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/42.jpg)
42
Stats on SPsStats on SPs
![Page 43: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/43.jpg)
43
Query issuesQuery issues
Unnecessary resultsUnnecessary results Select with no where clauseSelect with no where clause Select * instead of selecting few Select * instead of selecting few
columnscolumns Queries not qualified properlyQueries not qualified properly
Poorly written queriesPoorly written queries Missing indexMissing index Old statisticsOld statistics Incorrect optimizationIncorrect optimization
Index Tuning WizardIndex Tuning Wizard
![Page 44: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/44.jpg)
44
Choosing indexChoosing index
Space utilized by indexSpace utilized by index Index maintenance overheadIndex maintenance overhead To cluster or notTo cluster or not
Bookmark lookupBookmark lookup Range scan(OLTP Vs OLAP)Range scan(OLTP Vs OLAP)
Index columnsIndex columns Short & with high selectivityShort & with high selectivity Often used in many statementsOften used in many statements CoveredCovered Multi index queryMulti index query
![Page 45: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/45.jpg)
45
Monitoring Query executionMonitoring Query execution
Analyze query execution planAnalyze query execution plan Analyze the right plan - connection Analyze the right plan - connection
settingssettings
spid, blocked, waittype, cpu spid, blocked, waittype, cpu physical_io, memusage, open_tran physical_io, memusage, open_tran from sysprocessesfrom sysprocesses
Execution plan, read, write, duration Execution plan, read, write, duration in SQL Profilerin SQL Profiler
![Page 46: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/46.jpg)
46
Monitoring Query executionMonitoring Query execution
Stats I/oStats I/o Stats timeStats time Write your own traceWrite your own trace
Expected time / thresholdExpected time / threshold
WorktableWorktable Scan Vs SeekScan Vs Seek
![Page 47: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/47.jpg)
47
Query executionQuery execution
Query HintsQuery Hints Join, Index, Lock, ProcessingJoin, Index, Lock, Processing
Distributed query executionDistributed query execution TOP / SET ROWCOUNTTOP / SET ROWCOUNT FASTFIRSTROWFASTFIRSTROW IN / OR and subqueryIN / OR and subquery Selecting with aliasSelecting with alias
![Page 48: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/48.jpg)
48
Using stored proceduresUsing stored procedures
Cached execution planCached execution plan Recompiled when stats changesRecompiled when stats changes Warning: Wrong Parameter to stored Warning: Wrong Parameter to stored
procproc Binding parameter by positionBinding parameter by position Set nocountSet nocount sp_executesqlsp_executesql Auto-parameterization in SQL7Auto-parameterization in SQL7 sp_recompilesp_recompile
![Page 49: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/49.jpg)
49
Table statisticsTable statistics
Auto update statsAuto update stats Rowmodctr and StatVersionRowmodctr and StatVersion Fullscan and samplingFullscan and sampling
Auto column statsAuto column stats Explicit update statsExplicit update stats Explicit column statsExplicit column stats sp_autostatssp_autostats Queue / log tableQueue / log table sp_recompilesp_recompile
![Page 50: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/50.jpg)
50
Tuning Expensive SQL Tuning Expensive SQL StatementsStatementsPoor
statementPoor
statement
DDICinfo
DDICinfo
SQLExplain
SQLExplain
Whereused listWhere
used list
Is there asuitableindex?
Is there asuitableindex?
GoodOptimizerdecision?
GoodOptimizerdecision?
Inefficientcoding?
Inefficientcoding?
UpdatestatisticsUpdate
statistics
Re-codeRe-code
Yes
Yes
Yes
No
Index statistics
up to date?
Index statistics
up to date?
No
No
Re-codeor
change index
Re-codeor
change index
Yes
Statistics page
Statistics page
Autoupdatestats on?
Autoupdatestats on?
Switch onauto updstats
Switch onauto updstats
Yes
No
![Page 51: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/51.jpg)
51
LabLab
Improve the slow running reportImprove the slow running report Use ST05Use ST05 Stats on SPs(ST04)Stats on SPs(ST04) And other methodsAnd other methods
![Page 52: Microsoft SQL Server Administration for SAP Performance Monitoring and Tuning](https://reader035.vdocuments.us/reader035/viewer/2022081506/56649d7e5503460f94a60ec3/html5/thumbnails/52.jpg)
52
SummarySummary
Write module summaryWrite module summary