Transcript Insert Title Here
Practical Use of MDA Tables
A Case-Study in the Practical Use of MDA Tables from Sybase's Internal IT/DBA Team
David Burgess
Sybase Staff DBA
Agenda
• • • • • • • • DBA Nightmares ASE Performance and Tuning Problems • The ASE is slow, fix it!
How MDA Tables can help How MDA Collector can help Performance and Tuning with MDA Collector MDA Solutions: Practical applications of MDA Tables and Collector Common Problems with MDA Tables Q&A
Presentation Goal: Fixing DBA Nightmares
• • • • • • • • •
Black Box ASE Black Box Applications connecting to the ASE Hung query Hung ASE Query hogging 100% CPU Query crashing ASE Query resulting in massive blocking Problem logins running problem SQL Runaway SQL consuming log
MDA Quick Review
• • • MDA tables were introduced in ASE 12.5.0.3
• MDA = Monitoring and Diagnostic Access • Also called “monitoring tables” 41 tables installed ‘master’ database (15.0.2) • Table names begin with “mon” (e.g., monProcessActivity) • Can be accessed with standard SQL commands • Table data is generated from in-memory structures • No data is stored in master database • • Tables are proxy tables No license needed: included in ASE base product MDA tables provide access to detailed monitoring data • Process Activity • CPU usage, IO activity, resource usage • Resource usage • Data cache, procedure cache, engines • Object usage • Tables, partitions, indexes, stored procedures • Query history • SQL text, statement metrics, query plans, errors
How MDA Tables can help
• •
Identify, track and fix:
• • • • • • • Root cause of many ASE P&T issues Bad SQL that impacts performance Rogue processes hogging resources (CPU, IO, etc.) Bad stored procedures Bad indexes Problem applications and logins Unused objects (tables, indexes)
MDA Tables provide a lot of data
• Up until recently, ASE did not provide many ready-to-run sprocs to sort that data into information
How MDA Collector can help
•
Why Setup MDA Collector?
• • Collector tables can be used as ASE baseline Problems always happen when DBA is away from desk; Collector is always working • Collector should be setup to gather enough info to catch the problem when it happens • Use the Collector reports to review errors • • Review last few “collections” just before ASE problem Archive Collector reports for root cause analysis
How MDA Collector can help
• •
MDA Collector Reports
• MDA Collector has two dozen ready-to-run reports to help ‘translate’ MDA data into information • Use the Collector reports to review errors • • Review last few “collections” just before ASE crash or other error Archive Collector reports for root cause analysis
Run Collector reports to generate a new baseline
• • New Baseline required after any change Compare new baseline with old baseline to measure performance improvements
How MDA Collector works
• • • Can be configured to collect on three options • •
Application
SPID running longer than
15
seconds
System
info every
60
seconds: Engine Counters, Device, syslogs, contention...
•
Historical
SQL, Errorlog, Deadlocks... every
15
seconds Can be set to collect ‘snapshot’ for any frequency May set to archive for any number of days • Example: Prune everything older than
7
days • Techwave 2006 presentation on MDA collector reference: • http://www.sybase.com/techwave/techwave2006
How to setup MDA Collector
• • • • •
Enable MDA tables and grant ‘mon_role’ permission Create Collector database Install ASE sprocs and schema from zip-files
• http://www.sybase.com/techwave/techwave2006
Copy start_scripts from zip-files onto ASE host Run start_script and purge_script
How to startup MDA Collector
• • START_MDA to call all 3 'start' sprocs • Makes 3 user connections to ASE as the mda_collect user • dbadb_metrics..sp_dba_MON_SYSTEM_control 'start‘ • • dbadb_metrics..sp_dba_MON_APP_control 'start' dbadb_metrics..sp_dba_MON_HIST_control 'start' Those sprocs then wait in infinite loop until 'stop‘ • A snapshot is collected at every specified
control_table
interval • How to make it work for your version • Edit control table : …to collect what? …and how often?
• Edit Collector sprocs to add additional “filters”
sp_dba_MON_SYSTEM_col_v1252 sp_dba_MON_APP_col_v1252 sp_dba_MON_HIST_col_v1252
WHERE ml.SPID = mp.SPID
AND ml.KPID = mp.KPID
AND mp.SPID <> @v_my_SPID --AND mp.DBID <> db_id("dbadb_metrics") --AND mp.DBID <> db_id("sybsystemprocs") --AND mp.Login <> "mda_collect"
How to use MDA Collector
•
Example Collector Reports:
• HIST PROCS dbasp_MONR_HIST_DeadLock dbasp_MONR_HIST_ErrorLog dbasp_MONR_HIST_SQLSmt dbasp_MONR_HIST_SQLText HIST & APP show minutes, SYS is hour Captures all deadlocks and can go back in history All errors caused by SPIDS.
All statements and associated CPU, Read, Writes, Network activity etc.
Recreate all the SQL executed, can be limited to a particular SPID • SYS PROCS dbasp_MONR_SYS_Engine_Stats dbasp_MONR_SYS_OpenDB_Stats dbasp_MONR_SYS_SysWaits_Stats dbasp_MONR_SYS_CachedObj_Stats Collected every 180 Seconds, allows us to identify peak times at a granular level.
DB activity, logs contention .. tune syslogs and tempdb, could be used to justify multiple tempdbs.
Context Switches,. Allows us to identify what kind of resources are limiting the ASE. Used to identity if a user defined cached is being used or sized right ?
• APP PROCS dbatb_MON_App_Locks dbatb_MON_App_ProcessActivity dbasp_MONR_APP_Process_Waits dbasp_MONR_APP_Process_Smt dbasp_MONR_APP_Process_SQLText dbasp_MONR_APP_Process_Procs Similar to lock history, will identify which spid are holding locks on which objects during the snapshot.
Will identify the source of the SPID (IP Address) Shows what the SPID waiting for Information on Statements executing, e.g. CPU, Reads/Writes, source of statement etc., Network activity.
Catches all SQL text, for queries running longer that interval (15 seconds currently) Procedures running longer than 15 secs, and currently executing during the snapshot.
MDA Collector : Tips & Tricks
• •
Consolidating MDA Collector START/STOP scripts to one central host Consolidating MDA Collector data to one central Reporting ASE
• • Set RPC or loopback to the reporting ASE Run Collector reports on Reporting rather than OLTP • Future Direction of DBA team for MDA Collector
ASE Performance and Tuning: ASE is slow, fix it!
•
DBA Solutions :
• • Slower than what? Compare against a baseline • Any recent system changes : upgrades, new hardware Identify the problem: system resource …or… sql and query plan • • Rogue transaction hogging resources or blocking users Users blocked on tempdb • Make one change at a time and compare against previous baseline
P & T with MDA Collector : Where to Start?
• Gather baseline of ‘normal’ performance • Advise smaller intervals of about 15 – 20 minutes • • Get baseline for peak times and non-peak times Keep archive of baseline samples
dbasp_MONR_SYS_SysWaits_Stats 1
178 214 215 251 51 29 266 31 41 55
WaitEventID
250 179 260 61 19 57 104 150 36 52 124 209
SUM_WaitTime
35419 13867 13680 10017 7081 7079 3541 3522 579 306 293 153 96 88 71 51 47 40 17 11 9 3
SUM_Waits
43653 288344 570 1797 118 65 118 833 30190 438260 40505 14839 16440 169 13061 707 8310 536 4483 991 344 1875 0 0 0 0 0 61 10 5 5 2 1 1 1 0 0
AVG_WaitTime
621 243 240 175 124 124 62 9 78 17 6 32
AVG_Waits
765 5058 10 31 2 1 2 14 529 7688 710 260 288 2 229 12 145
Description
waiting for incoming network data waiting while no network read or write is required waiting for date or time in waitfor command hk: pause for some time xact coord: pause during idle loop checkpoint process idle loop wait until an engine has been offlined waiting while allocating new client socket waiting on run queue after yield waiting on run queue after sleep waiting for network send to complete waiting for last i/o on MASS to complete waiting for regular buffer read to complete waiting for message in worker thread mailbox waiting for buf write to complete before writing wait to acquire latch wait for i/o to finish after writing last log page waiting for a lock waiting for MASS to finish writing before changing waiting for i/o on MASS initated by another task wait for mass read to finish when getting page waiting for a pipe buffer to read
P & T with MDA Collector : Where to Start?
• Reading that Baseline • Start with monProcessWaits/monSysWaits • Info on whether the next step is query related, client software, hardware or contention in ASE • • If you know this to be SQL query related, you may be able to skip monProcessWaits and go directly to monProcessActivity/ monProcessStatement/monSysStatement • Most closely approximates sp_sysmon context switching section • …but gives you the details not available with sysmon • …and lets you focus down to the process detail level Unfortunately, the “WaitEvents” require explanation (see later slide) • Comparing Baselines • Compare Apples-to-Apples : • Remember to compare an equivalent timeframe • Sample interval, peak periods vs. peak periods
P & T with MDA Collector : What to look for?
•
Focus on the "Waits"
• • • Log, Tempdb, data IO, WaitEvents Use MS Excel or OpenOffice to plot Requests vs. Waits Look at monOpenObjectActivity for explanation •
Sort report output
• Sort by : CPUTime, WaitTime, PhysicalReads, LogicalReads, PagesRead, PhysicalWrites, PagesWritten, MemUsageKB • Remember that SPIDs report total of every and any sql every executed by that spid. • If same spid logged in for weeks, then those report numbers will be very high
P & T with MDA Collector : Good wait events
• dbasp_MONR_SYS_SysWaits_Stats 1 35 37 53 83 85 91 52 124 209 54 334
215 251
51 29 266 31 41 55 150 36
WaitEventID 250 179 260 61 19 57 104 178 214
11 9 3 1 1 0 0 0 0 0 0 71 51 47 40 17
306 293
153 96 88
SUM_WaitTime 35419 13867 13680 10017 7081 7079 3541 3522 579
0 8 0 0 0 0 991 344 1875 158 307
SUM_Waits 43653 288344 570 1797 118 65 118 833 30190 438260 40505
14839 16440 169 13061 707 8310 536 4483 0 0 0 0 0 0 0 0 0 0 0
5 5
2 1 1 1 0 0 0 0
AVG_WaitTime 621 243 240 175 124 124 62 61 10
17 6 32 2 5 0 0 0 0 0 0
7688 710
260 288 2 229 12 145 9 78
AVG_Waits 765 5058 10 31 2 1 2 14 529 Description waiting for incoming network data waiting while no network read or write is required waiting for date or time in waitfor command hk: pause for some time xact coord: pause during idle loop checkpoint process idle loop wait until an engine has been offlined waiting while allocating new client socket waiting on run queue after yield waiting on run queue after sleep waiting for network send to complete
waiting for last i/o on MASS to complete waiting for regular buffer read to complete waiting for message in worker thread mailbox waiting for buf write to complete before writing wait to acquire latch wait for i/o to finish after writing last log page waiting for a lock waiting for MASS to finish writing before changing waiting for i/o on MASS initated by another task wait for mass read to finish when getting page waiting for a pipe buffer to read waiting for write of the last log page to complete waiting for Lava pipe buffer for write waiting for buffer validation to complete wait for MASS to finish changing before changing waiting for MASS to finish changing to start i/o wait for DES state is changing wait for flusher to queue full DFLPIECE waiting for disk buffer manager i/o to complete
P & T with MDA Collector : More wait events to watch for…
• dbasp_MONR_SYS_SysWaits_Stats 1 85 91 92 99 143 157 169 197 200 201 202 203 205 207 230 36 52 124 209 54 334 35 37 53 83
WaitEventID
… 51 29 266 31 41 55 150
SUM_WaitTime
17 11 9 3 1 153 96 88 71 51 47 40 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0
SUM_Waits
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 307 0 8 0 0 14839 16440 169 13061 707 8310 536 4483 991 344 1875 158
AVG_WaitTime
2 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
AVG_Waits
78 17 6 32 2 260 288 2 229 12 145 9 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0
Description
waiting for last i/o on MASS to complete waiting for regular buffer read to complete waiting for message in worker thread mailbox waiting for buf write to complete before writing wait to acquire latch wait for i/o to finish after writing last log page waiting for a lock waiting for MASS to finish writing before changing waiting for i/o on MASS initated by another task wait for mass read to finish when getting page waiting for a pipe buffer to read waiting for write of the last log page to complete waiting for Lava pipe buffer for write waiting for buffer validation to complete wait for MASS to finish changing before changing waiting for MASS to finish changing to start i/o wait for DES state is changing wait for flusher to queue full DFLPIECE waiting for disk buffer manager i/o to complete waiting for synchronous disk buffer manager i/o wait for data from client pause to synchronise with site manager wait for object to be returned to pool wait for message waiting for read to complete in parallel dbcc waiting for page reads in parallel dbcc waiting for disk read in parallel dbcc waiting to re-read page in parallel dbcc waiting on MASS_READING bit in parallel dbcc waiting on TPT lock in parallel dbcc waiting sending fault msg to parent in PLL dbcc waiting for site handler to complete setup
P & T with MDA Collector : Wait events to watch for…
•
Wait event descriptions
• • • • • • • • • •
Where to look
“waiting to be scheduled” “waiting for a disk read to complete” “waiting for a disk write to complete” (cpu) (read) (write) “waiting to acquire the log semaphore” (log contention) “waiting to take a lock” (lock contention) “waiting for memory or a buffer” “waiting for input from the network” (address contention) (client speed) “waiting to output to the network” “waiting for internal system event” “waiting on another thread” (client fetch/net stat) (PLC, index balance) (contention)
P & T with MDA Collector : Wait events to watch for…
• • • • • • • • • • • • • • • • • • • •
Client Related S/W Issues
171 : Waiting for CTLIB event to complete 251 : Waiting for network send to complete 250 : Waiting for incoming network data -waiting on next Client command to be sent -waiting for ct_sendpassthru(), Equivalent to ‘awaiting command’
Transaction Log Delays
259 : Waiting until last chance threshold is cleared 150 : Waiting for semaphore 54 : waiting for write of the last log page to complete 55 : wait for i/o to finish after writing last log page -Transaction log keeps filling and crossing the LCT -you are waiting to write to the last log page -you are waiting for the last log page you wrote to flush
Contention
41 : Wait to acquire latch 70 : Waiting for semaphore -Address locking contention (tran log) -Typically normal row/pg lock, but could be log semaphore or spinlock contention 52 : "waiting for last MASS on which i/o was issued by some other task" -Two nearly concurrent select/into's in tempdb
H/W Issues: CPU contention
214 : Waiting on run queue after yield 215 : Waiting on run queue after sleep 33 : Wait for buffer read to complete 34 : Wait for buffer write to complete 179 : waiting while no network read or write is required Waiting for disk write to complete in memory scan, join operations, sorting, looping logic in proc, etc.
Slow cpu's could result in higher waits on log semaphore and disk writes -Logical read or network read -Logical write (update in cache before disk flush)/network send -Netserver checked and no network read/write pending -Exceeded disk i/o structures and delayed for pending i/o queue
P & T with MDA Collector : Where to look?
• • • • • • • • • • •
monCachedObject
•
- dbasp_MONR_SYS_CachedObj_Stats
Provides statistics for all objects and indexes that currently have pages cached within a data cache
monCachePool
• Provides statistics for all pools allocated for all caches
monOpenObjectActivity
• Provides statistics for all open objects
- dbasp_MONR_SYS_CachePool_Stats - dbasp_MONR_SYS_OpenObj_Stats monIOQueue
•
- dbasp_MONR_SYS_IOQueue_Stats
Provides device IO statistics broken down into data and log IO, for normal and temporary databases on each device.
monDeviceIO
• Provides statistical information about devices
- dbasp_MONR_SYS_DeviceIO_Stats monSysWaits
• Provides a server-wide view of events that processes are waiting for
- dbasp_MONR_SYS_SysWaits_Stats monProcessActivity
• Provides statistics about process activity
monSysStatement
• Provides statistics for the most recently executed statements.
- dbasp_MONR_APP_Proc_Activity - dbasp_MONR_HIST_SQLSmt monCachedProcedures
•
- dbasp_MONR_APP_Process_Procs
Provides statistics about all procedures currently stored in procedure cache
monEngine
• Provides statistics regarding ASE engines
- dbasp_MONR_SYS_Engine_Stats monProcedureCacheModuleUsage
• Provides procedure cache usage statistics by ASE modules
P & T with MDA Collector : What next?
• • • • • • Step 1: Gather current statement statistics • Check monProcessStatement :
dbasp_MONR_APP_Process_Smt
• Check monProcessSQLtext :
dbasp_MONR_APP_Process_SQLText
Step 2: Get SPID Resource Consumption • Check monProcessActivity :
dbasp_MONR_APP_Proc_Activity
Step 3: If High Wait Time – Find cause • Check monProcessWaits :
dbasp_MONR_APP_Process_Waits
Step 4: If High I/O Write waits or Tempdb is suspect • Check monProcessObject :
dbasp_MONR_APP_Process_Object
• Check monOpenObjectActivity :
dbasp_MONR_SYS_OpenObj_Stats
Step 5: If Contention • Check monOpenObjectActivity to find table(s) with most contention (LockWaits) • • Check monProcess for Blocking Check monLocks, monDeadLocks :
dbasp_MONR_APP_Proc_Activity
:
dbasp_MONR_APP_Lock_History
Step 6: If Proc (somewhere in proc is slow) • • monProcessStatement only gives metrics on
current
statement within the
current
batch/context/line monSysStatement – historical view of the query tree :
dbasp_MONR_HIST_SQLSmt
MDA Collector Solutions Case Study 1: Tempdb full, ASE sprocs hang
select * from master..monProcessSQLText
369 1102774818 5 4 9 1 and a.fieldname = 'res_status' 369 1102774818 5 4 10 1 and a.newval = 'Closed' 369 1102774818 5 4 11 1 and bug_id = bugs.bug_id 369 1102774818 5 4 12 1 ) as report_dt 369 1102774818 5 4 13 1 into #bug_closed 369 1102774818 5 4 14 1 from bugs 369 1102774818 5 4 15 1 where 369 1102774818 5 4 16 1 bug_id in ( 369 1102774818 5 4 17 1 select sy_addl_case.bug_id 369 1102774818 5 4 18 1 from 369 1102774818 5 4 19 1 sy_addl_case join clarify..table_case on 369 1102774818 5 4 20 1 left(sy_customer_case,8)=id_number 369 1102774818 5 4 21 1 join bugs b on b.bug_id = sy_addl_case.bug_id 369 1102774818 5 4 22 1 where 369 1102774818 5 4 23 1 sy_customer_case is not null 369 1102774818 5 4 24 1 and sy_customer_case <> '10001' 369 1102774818 5 4 25 1 and active_flag <> 2 369 1102774818 5 4 26 1 and cure_code = '1' 369 1102774818 5 4 27 1 and b.sy_product_family in ( 'SQL Server','Docs: SQL Server') 369 1102774818 5 4 28 1 and b.bug_type in ('Regression', 'Defect') 369 1102774818 5 4 29 1 and b.bug_severity in (0, 1) 369 1102774818 5 4 30 1 )
MDA Collector Solutions Case Study 2: ASE stacktrace and crash
• ASE stacktrace and die on spid
287
00:00000:00
287
:2008/06/13 14:19:03.61 kernel Stack overflow detected: limit: 0x0000010003249290, sp: 0x0000010003249388 00:00000:00
287
:2008/06/13 14:19:03.62 kernel pc: 0x0000000080f84ea0 pcstkwalk+0x24(0x0000010003249208, 0x0000010003248580, 0x000000000000270f, 0x0000000000000002, 0x0000000000000250) 00:00000:00
287
:2008/06/13 14:19:03.62 kernel pc: 0x0000000080f84d04 ucstkgentrace+0x1c0(0x000001000be2ff18, 0x0000000000000002, 0x000000000000270f, 0x0000000000000000, 0x0000000000000000) • Use Collector to find out what was spid
287
doing ? • dbasp_MONR_HIST_SQLText 60, null, null ,
287
KeyCounter SampleTime SPID KPID BatchID SequenceInBatch SQLText ----- ----- ----- ----- ----- ----- ----- ----- 15244 6/13/08 2:18 PM
287
15244 6/13/08 2:18 PM
287
250937603 12 1 select * from x_debug_on 250937603 13 1 select name from master.dbo.sysdatabases 15244 6/13/08 2:18 PM
287
id_number = @subcase_num 250937603 14 1 UPDATE table_subcase SET x_eng_prty_rating = "40"\t\t WHERE Turns out that the sp_configure 'allow nested triggers' option was enabled on ASE. This results in a trigger + sproc infinite loop = stacktrace and crash.
MDA Collector Solutions Case Study 3: Bad Sprocs
SPID 121 121 121 121 • Use MDA to find problem sprocs • dbasp_MONR_APP_Process_Object KPID 23855236 23855236 23855236 23855236 Login tools_user tools_user tools_user tools_user Application tools tools tools tools Command UPDATE UPDATE UPDATE UPDATE MasterTranID $upd $upd $upd $upd ObjectID 466012129 466012129 1327447903 1823449670 OBJ_NAME [NULL] [NULL] work_nav_names work_nav_trees IndexID
LogicalReads
0 0 0 1 0 1015 0
51797903
SPID 121 • dbasp_MONR_APP_Process_Procs KPID 23855236 Login tools_user Application tools Command UPDATE MasterTranID $upd OwnerUID 1 ObjectID OBJ_NAME 276352199 p_tools_get_authorized_path • • Use MDA to find stored Procedure Performance Averages Use Collector to find sproc averages over historical timeline…
MDA Collector Solutions Case Study 4: Bad Cache
•
Use MDA to show key columns in monStatementCache
• TotalSizeKB, NumStatements, NumSearches, HitCount, NumRemovals 1> select * from monStatementCache 2> go TotalSizeKB UsedSizeKB NumStatements NumSearches HitCount NumInserts NumRemovals NumRecompilesSchemaChanges NumRecompilesPlanFlushes ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------------------------- ----------------------- 40000 37800 1329 529133 138721 390412 421693 12 1398 •
KeyCounter
3793 3794 3795 3796 3797 3798 3799
Use Collector to show cache stats over time…
• dbasp_MONR_SYS_DataCache_Stats 1 , null, null, null, 'summary'
SampleTime PhysicalWrites
7/7/08 4:10 PM 1 7/7/08 4:12 PM 1 7/7/08 4:13 PM 1 7/7/08 4:15 PM 1 7/7/08 4:16 PM 1 7/7/08 4:18 PM 1 7/7/08 4:19 PM 1
CacheID Stalls
2 app_cache 2 app_cache 2 app_cache 2 app_cache 2 app_cache 2 app_cache 2 app_cache 0 Y 0 Y 0 Y Y 0 Y 0 Y
RelaxedReplacement CachePartitions CacheName
0 1 Y 0 97 1 96 1 93 1 93 1 94 1 93 1 92 0 0 0 0 0 0 0 0 0 0 0
BufferPools Accurate
0 0 0 0 0 0 0 0 0
CacheSearches SamplePeriod
0
PhysicalReads Hit Ratio
0 0 0 0 0 0 0 0 0 0 0 0 0
LogicalReads
0 0 0 0 0 0 0 0
MDA Collector Solutions Case Study 5: Baseline for setting resource limits
•
Use Collector to find “high-watermark” of sql
• Can then use this information to set realistic resource limits • dbasp_MONR_APP_Proc_Activity, dbasp_MONR_APP_Process_Object SPID 590 Application
isql
ObjectID 298132503 OwnerUserID 1609
LogicalReads 824100
• LogicalReads ~ resource limit io_cost sp_help_resource_limit name appname rangename rangeid limitid limitvalue enforced action scope ---- ------- ------------ ------- ------- ---------- -------- ------ ---- NULL
isql
at all times 1 4
1024000
2 2 1
MDA Collector Solutions Case Study 6: Bad SQL
• • Use MDA to find most I/O intensive statement select * into #ts from master..monSysStatement
select KPID, BatchID, LineNumber, LogicalReads, Elapsed = datediff(ms, StartTime, EndTime) from #ts where LogicalReads > 100 order by 4 desc KPID BatchID LineNumber LogicalReads Elapsed ---------- ---------- ---------- ----------- ---------- 574619857 9 13 5509405 68613 575602932 10 3 360656 7956 575209751 10 3 86241 606 575406493 10 3 59546 983 576258422 2 62 39963 223 575275534 1 62 39959 216 575930476 2 62 39955 250 332857800 65454 1 15884 1600 575275534 9 1 12758 176 Use Collector to find most I/O intensive statement in last 2 hours… dbasp_MONR_APP_Proc_Activity 120 , null, null, null, 'ANALYZE'
MDA Collector Solutions Case Study 7: Archiving unused objects
• • Use MDA to find old unused tables to be archived Unused indexes that be dropped or impacting performance Find Indexes that have not been used since the server was started select DB = convert(char(20), db_name()), TableName = convert(char(20), object_name(i.id, db_id())), IndexName = convert(char(20),i.name), IndID = i.indid
from master..monOpenObjectActivity a, sysindexes i where a.ObjectID =* i.id
and a.IndexID =* i.indid
and (a.UsedCount = 0 or a.UsedCount is NULL) and i.indid > 0 and i.id > 99 -- No system tables order by 2, 4 asc • Use Collector to find old unused tables over last few months….
• Archive Collector reports for a few Quarters
MDA Collector Solutions Case Study 8: Hot tables
• Use MDA to find Hot Tables and Indexes select db_name = db_name(DBID), table_name = object_name(ObjectID, DBID), IndexID, LogicalReads, PhysicalReads, Operations, LockWaits from master..monOpenObjectActivity order by LogicalReads desc DBName TableName IndexID LogicalReads PhysicalReads Operations ------------------------------ ----------------------------- ---------- ----------- ------------ ---------- qts_db bugs qts_db sy_resolution 0 1858361243 181866 0 1534176756 16555 3526752 18742290 qts_db e2_cset qts_db x_bugs_nc1 qts_db sy_product_name qts_db sy_resolution_activity qts_db sn_activity_details qts_db doc_xref 0 1462264415 1347 2 1023824797 332059 0 930762883 145 0 873960390 408227 0 675669657 726 0 396974905 3098 1307975 0 799079 229939 17946 6262053 • Use Collector to find tables usage over time… • dbasp_MONR_SYS_OpenObj_Stats
Common Problems with MDA Tables
• Overconfiguration of MDA Table options could result in problems in the master database.
• • • Keep the MDA Table options realistic Turn on only what you need Another benefit with Collector; smaller pipe config is enough • • • • • • exec sp_configure "sql text pipe max messages",100
exec sp_configure "plan text pipe max messages",100
exec sp_configure "statement pipe max messages",100 exec sp_configure "errorlog pipe max messages",100 exec sp_configure "deadlock pipe max messages",100 exec sp_configure "max SQL text monitored",2048
Common Problems with MDA Tables (Cont.)
• • • • Excessive Polling • • • E.g. sampling every second If more than every minute, you'd better have a real good reason Drives cpu & network I/O artificially high Collecting Everything for Everybody • • Instead of using MDA parameters (especially SPID & KPID) "turn it all on and wait for magic to happen” Joining MDA tables (or subqueries) • Accuracy problems if self-joins, subqueries – even normal joins Enabling pipe tables too early • Determine that you have a bad query before looking for it
Common Problems with MDA Tables (Cont.)
• •
Performance problems with the following options Enable only if requried for specific problem
• • exec sp_configure "plan text pipe active" exec sp_configure "plan text pipe max messages“ • • exec sp_configure "sql text pipe active" exec sp_configure "sql text pipe max messages” • • • exec sp_configure "statement pipe max messages” exec sp_configure "statement statistics active” exec sp_configure "per object statistics active”
Common Problems with MDA Tables (Cont.)
• • MDA table changes between ASE versions Install script after ASE upgrade • Internal Ct-Lib/Cs-Lib error 33620240: 'cs_convert: cslib user api layer: external error: Conversion between 8 and 12 datatypes is not supported.‘ • Must re-run requried $SYBASE/$SYBASE_ASE/scripts/installmontables • • ASE 15.0.2, this script is part of 'installmaster' Do NOT run this script with 'sqsh‘. 'sqsh' sees a '$' as the start of a sqsh variable, and this messes up the native RPC names, since these RPC names start with a '$' as well.
Common Problems with MDA Tables (Cont.)
•
Arithmetic overflow for multiple engines while using MDA monEngine table - ContextSwitches field.
• "select sum(convert(numeric(18,0),ContextSwitches)) from master.dbo.monEngine“ •
Invalid MDA table data
• • Incorrect DBID Invalid columns : wait time
Common Problems with MDA Tables
• CR 343252, 367005, 446789 : Fixed in ASE 12.5.4 • Querying the monCachedObject table can cause other processes to timeslice resulting in performance hit.
• CR 427028 : Fixed in 12.5.4
• "ubo_object_from_slot:Pss Invalid slot id 0" … "current process infected with 11" in module "lock__print_deadlockchain" after ASE encounters a deadlock when "print deadlock information" is set or the "deadlock pipe active" MDA option is in use. • CR 446789 : Fixed in 12.5.4 ESD#5 • Enabling the 'per object statistics active' Tables configuration option may cause degradation in query performance.
Common Problems with MDA Tables
• •
CR#436330 : Fixed in 15.0.2 ESD#3 CR#336077 : Fixed in 12.5.1 ESD#2 , 15.0.2 ESD#5
• Under rare conditions querying the monOpenObjectActivity table may result in error 14108, or stacktrace, or crash • Workaround to not collect from monOpenObjectActivity table • update dbadb_metrics..dbatb_mon_control set Record = 'N‘ where KeyType = 'openobjectactivity‘ •
sp_sysmon with MDA
• • Must use sp_sysmon option "noclear" to preserve the counters sp_sysmon "00:01:00", noclear
Learn more about MDA
• •
ISUG
• http://www.sybase.com/sb_content/1027266/MDA_Tables_ISUG_Final.
pdf • http://download.sybase.com/presentation/isug_presentations/sybs31104 .wmv
• http://www.sybase.com/sb_content/1027266/SybaseISUG_MDA -042406.pdf
•
MDA Tables in ASE – Tips and Tricks Rob V’s 2004 Techwave Presentation
• •
http://www.sypron.nl/mda.html
Source for the MDA Collector
Q & A and Collector Demo
• •
Latest events from Sybase DBA team… MDA Collector Demo
• Examples of configuring Collector to Collect • • • • sp_configure parameters dbo.dbatb_mon_control table settings Collector table schema Collector sprocs • • Example Collector setup in our Dev environment Examples of running Collector Reports