SMS tablespaces have been deprecated for serveral releases. There isn’t a straight forward way to convert these to Automatic Storage Management tablespaces. A fast, reliable way to do this is to create an ASM Tablespace to replace the SMS tablespace, extract the DDL for the table to be moved using db2look, create the new table with a different name in the new tablespace and then use LOAD FROM CURSOR to load the table data into the new table. Upon completion, and verification of row counts from old to new, rename the old to old, and rename the new one to the old name. This method using load from cursor has been around since at least V8.2 of Db2 LUW. So get movin’ before it is too late. The above method can also be used to “rescue” an SMS tablespace that has run out of space..
Recently I have been contacted by several clients reporting various degrees of lock-related problems in OLTP systems. One of the first item I check is the setting of the DB CFG locktimeout configuration parameter. What I find is that many times the default of -1 is used which doesn’t allow for any lock timeouts. This is one of the first parameters you will want to change, a value of 10000 (10 secs) is a good starting point. Additionally, make sure that you are taking advantage of the lock avoidance registry variables ( Covered in a future post). Here are a few links to some info to help you along the way with understanding Db2 lock isolation levels along with JDBC driver settings:
https://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2isolationlevel/
Review the locktimeout setting here: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0000329.html
Often you might need to know if a Db2 registry variable can be changed online and immediate, without requiring a Db2 instance restart. This can be done by using the -info option of the db2set command. See the example below, you should add this bit of info to your Db2 toolbox.
Example:
C:\Program Files\IBM\SQLLIB_01\BIN>db2set -info db2_capture_locktimeout
Immediate change supported : YES
Immediate by default : YES
C:\Program Files\IBM\SQLLIB_01\BIN>db2set -info db2_use_alternate_page_cleaning
Immediate change supported : NO
Immediate by default : NO
New in Db2 11.5, the Db2 Community Edition (takes the place of the former Db2 Express) is a great edition for developers to try out any of the plethora of development environments supported by Db2. Docker and standard downloads available. I have clients using this in production up to the license limits with superior capability. Use it in test or in small production environments, with full features. Hard to beat, with a seamless upgrade to higher editions later if needed. Read more at Phil’s article here: https://www.ibm.com/cloud/blog/announcements/ibm-db2-developer-community-edition
With Db2 11.1 and 11.5 there have been some edition changes. I get lots of questions regarding Db2 editions, features and licensing from my clients. I’m currently working with a client moving from Db2 Express-C and Db2 Express -FTL 10.5 to 11.5. Editions available in Db2 11.5 are Db2 Community, Standard Edition and Db2 Advanced Edition. You can download the 11.5 Announcement here For details on new editions and features provided, check out the following links:
https://www.ibm.com/products/db2-database/pricing
Today is the day that Windows 7 and Windows 2008 Server support ends….Also, Db2 10.5 is nearing EOS in September 2020, so get moving…..
https://www.ibm.com/support/pages/db2-distributed-end-support-eos-dates
https://support.microsoft.com/en-us/help/13853/windows-lifecycle-fact-sheet
Database Member 0 — Database XXXXXXX — Active — Up 377 days 05:05:17 — Date 2019-12-17-12.57.47.749000
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SUPERASYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = REMOTE_CATCHUP
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 10.221.37.1
PRIMARY_INSTANCE = XXXXXXX
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 10.221.37.2
STANDBY_INSTANCE = XXXXXXX
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 07/15/2019 11:41:45.384342 (1563205305)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 1
HEARTBEAT_EXPECTED = 1085939
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 15
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
PRIMARY_LOG_FILE,PAGE,POS = S0742992.LOG, 866, 97283366969536
STANDBY_LOG_FILE,PAGE,POS = S0742992.LOG, 746, 97283366479849
HADR_LOG_GAP(bytes) = 1224
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0742992.LOG, 746, 97283366479849
STANDBY_RECV_REPLAY_GAP(bytes) = 965
PRIMARY_LOG_TIME = 12/17/2019 12:57:47.000000 (1576605467)
STANDBY_LOG_TIME = 12/17/2019 12:57:32.000000 (1576605452)
STANDBY_REPLAY_LOG_TIME = 12/17/2019 12:57:32.000000 (1576605452)
STANDBY_RECV_BUF_SIZE(pages) = 1048575
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 5040000
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N
This payments processing database with standby read only enabled is quite busy and it regularly is up for over a year or so… DB2 Workgroup Server Edition, DB2 10.5 FP10, Windows Server. #IBMchampion #Db2 #IDUGdb2
Database Member 0 — Database XXXXXXXX — Active — Up 339 days 03:03:39 — Date 2019-11-09-10.54.20.617000
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SUPERASYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = REMOTE_CATCHUP
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 10.221.37.1
PRIMARY_INSTANCE = XXX
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 10.221.37.2
STANDBY_INSTANCE = XXX
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 07/15/2019 11:40:48.347241 (1563205248)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 2
HEARTBEAT_EXPECTED = 976254
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 8
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 8192
PRIMARY_LOG_FILE,PAGE,POS = S0139127.LOG, 4736, 4323093384568
STANDBY_LOG_FILE,PAGE,POS = S0139127.LOG, 4736, 4323093380945
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0139127.LOG, 4736, 4323093380945
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 11/09/2019 10:54:14.000000 (1573314854)
STANDBY_LOG_TIME = 11/09/2019 10:54:11.000000 (1573314851)
STANDBY_REPLAY_LOG_TIME = 11/09/2019 10:54:11.000000 (1573314851)
STANDBY_RECV_BUF_SIZE(pages) = 8192
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 3200000
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N
I served as a technical reviewer on this book and many others. This was a great reference at the time and is still good for many aspects of Db2. #Db2 #IBMchampion #IDUGdb2
Note: This is just one of the various ways I capture and tune SQL. I use many different methods to capture suboptimal SQL depending on the urgency of the situation and the details I require to grab the SQL and work on a solution. It is not all encompassing. Db2 Design Advisor is your friend, and in my experience is not used enough!!! But the hard part comes when you have captured a suboptimal SQL and Design Advisor doesn’t have any recommendations!!!! At this point you can’t just throw your hands up and give up, it has to be fixed!!! This is where we shine with our clients, there isn’t an SQL out there that we can’t improve using all of the knowledge we have and all the tools in our toolbox. I’ll post more on SQL rewrite possibilities, index design and redesign, how to capture suboptimal SQL, statistics effects on optimization, statistical views, informational constraints, and hints, to name a few….
- Connect to database and run “db2 call monreport.pkgcache” to get top statements.
— Command options —
CACHE_INTERVAL: All statements
SECTION_TYPE: Dynamic and static SQL
MEMBER: All
================================================================================
Part 1 – Summaries by ‘top’ metrics
Top 10 statements by TOTAL_CPU_TIME
——————————————————————————–
# TOTAL_ STMT_TEXT
CPU_TIME
— ———– —————————————————————
1 217980426 INSERT INTO SPECTRUM_RESULT_5GHZ (WHEN,MEAS_ID_FK,EYE_ID,ANT_ID
2 137210647 select MEAS_ID from final table (INSERT INTO CLIENT_PCAP_RADIO
3 33451937 INSERT INTO SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL,
4 24715177 INSERT INTO SPECTRUM_RESULT (WHEN,MEAS_ID_FK,EYE_ID,ANT_ID,SUBC
5 24691176 select MEAS_ID from final table (INSERT INTO RADIO_ENVIRONMENT_
6 18695817 INSERT INTO AP_FTP_QOS_TEST (WHEN,MEAS_ID,NW_ID,AP_ID,EYE_ID,AN
7 17015041 select MEAS_ID from final table (INSERT INTO AP_PCAP_RADIO (WHE
8 16529630 select MEAS_CATEGORY_ID from final table (INSERT INTO AP_FTP_QO
9 8262363 select MEAS_ID from final table (INSERT INTO CLIENT_PCAP_QOS (W
10 4794274 select MEAS_CATEGORY_ID from final table (INSERT INTO MOS_QOS_T
- Go to topsql.sql script to get full SQL for above insert:
2017-09-22-13.09.08.775452 14586120 14586087
0 5465 14507 1
1 2 2 2 INS
ERT INTO SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL, HT_MODE, BEACON_OK,
SIGNAL_LEVEL_APEYE, SNR_APEYE, QUALITY, MAXQUAL, DURATION, WHEN ) VALUES ( ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
- Then format the SQL for explain design advisor usage:
INSERT INTO CARAT7.SCAN_RADIO (NW_ID, AP_ID, EYE_ID, ANT_ID, CHANNEL,
HT_MODE, BEACON_OK,
SIGNAL_LEVEL_APEYE, SNR_APEYE, QUALITY,
MAXQUAL, DURATION, WHEN )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
- Run db2advis [db7sign@east1 ~]$ db2advis -d meas7 -i iscanradio.txt | more
Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2017-09-22-12.35.55.976835
found [1] SQL statements from the input file
Recommending indexes…
0 indexes in current solution
[ 11.0000] timerons (without recommendations)
[ 11.0000] timerons (with current solution)
[0.00%] improvement
—
—
— LIST OF RECOMMENDED INDEXES
— ===========================
— no indexes are recommended for this workload.
—
—
— RECOMMENDED EXISTING INDEXES
— ============================
—
—
— UNUSED EXISTING INDEXES
— ============================
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_WHEN”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APEYEWHENBEACON”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APBEACONWHENNWSIG”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYENWBEACONWHENSIG”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APNWBEACONWHENSIG”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_NWBEACONWHENSIG”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APBEACONWHENNWSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APNWBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_NWBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYENWBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APBEACONWHENNWCH”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_NWBEACONWHENCH”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYENWBEACONWHENCH”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APNWWHENBEACON”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYENWWHENBEACONAP”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_WHENEYESIGHTCHANTAP”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APEYEANTBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYEANTBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYECHBEACONWHENSNR”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_APWHENNWBEACON”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_CH_ANTID_APID_DBI3”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3”;
— DROP INDEX “CARAT7 “.”I_SCAN_RADIO_EYEWHEN”;
— ===========================
—
0 record(s) selected.
- Review output and determine if index recommended or if not possible SQL rewrite or further manual analysis.
- Review unused indexes on the table in question.
[db7sign@east1 ~]$ db2 “select lastused, indname from syscat.indexes where tabname=’SCAN_RADIO'” | more
LASTUSED INDNAME
———- ———————————————————————
———————————————————–
09/21/2017 I_SCAN_RADIO_WHEN
09/22/2017 I_SCAN_RADIO_APEYEWHENBEACON
04/20/2017 I_SCAN_RADIO_APBEACONWHENNWSIG
04/17/2017 I_SCAN_RADIO_EYENWBEACONWHENSIG
09/22/2017 I_SCAN_RADIO_APNWBEACONWHENSIG
03/10/2017 I_SCAN_RADIO_NWBEACONWHENSIG
04/20/2017 I_SCAN_RADIO_APBEACONWHENNWSNR
11/04/2016 I_SCAN_RADIO_APNWBEACONWHENSNR
09/01/2017 I_SCAN_RADIO_NWBEACONWHENSNR
03/25/2017 I_SCAN_RADIO_EYENWBEACONWHENSNR
09/15/2017 I_SCAN_RADIO_APBEACONWHENNWCH
09/22/2017 I_SCAN_RADIO_NWBEACONWHENCH
09/08/2017 I_SCAN_RADIO_EYENWBEACONWHENCH
09/21/2017 I_SCAN_RADIO_APNWWHENBEACON
09/22/2017 I_SCAN_RADIO_EYENWWHENBEACONAP
09/07/2017 I_SCAN_RADIO_WHENEYESIGHTCHANTAP
03/31/2017 I_SCAN_RADIO_APEYEANTBEACONWHENSNR
09/14/2017 I_SCAN_RADIO_EYEANTBEACONWHENSNR
03/31/2017 I_SCAN_RADIO_EYECHBEACONWHENSNR
09/21/2017 I_SCAN_RADIO_APWHENNWBEACON
09/22/2017 I_SCAN_RADIO_CH_ANTID_APID_DBI3
09/22/2017 I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3
09/22/2017 I_SCAN_RADIO_EYEWHEN
23 record(s) selected.
- Review index definitions and compare to query.
Index Index Unique Number of Index Index Null Index Data Max Varchar Xml BUSINESS_TIME Column
schema name rule columns type partitioning keys ID type Hashed Length pattern WITHOUT OVERLAPS names
——————————————————————————————————————————– ——————————————————————————————————————————– ————– ————– ————————— ————– —— ————— ——————————————————————————————————————————– ——- ————— ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————– ——————– ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–
CARAT7 I_SCAN_RADIO_WHEN D 1 RELATIONAL DATA – Y 1 – – – – NO +WHEN
CARAT7 I_SCAN_RADIO_APEYEWHENBEACON D 4 RELATIONAL DATA – Y 2 – – – – NO +AP_ID+EYE_ID+WHEN+BEACON_OK
CARAT7 I_SCAN_RADIO_APBEACONWHENNWSIG D 5 RELATIONAL DATA – Y 3 – – – – NO +AP_ID+BEACON_OK+WHEN+NW_ID+SIGNAL_LEVEL_APEYE
CARAT7 I_SCAN_RADIO_EYENWBEACONWHENSIG D 5 RELATIONAL DATA – Y 4 – – – – NO +EYE_ID+NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE
CARAT7 I_SCAN_RADIO_APNWBEACONWHENSIG D 5 RELATIONAL DATA – Y 5 – – – – NO +AP_ID+NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE
CARAT7 I_SCAN_RADIO_NWBEACONWHENSIG D 4 RELATIONAL DATA – Y 6 – – – – NO +NW_ID+BEACON_OK+WHEN+SIGNAL_LEVEL_APEYE
CARAT7 I_SCAN_RADIO_APBEACONWHENNWSNR D 5 RELATIONAL DATA – Y 7 – – – – NO +AP_ID+BEACON_OK+WHEN+NW_ID+SNR_APEYE
CARAT7 I_SCAN_RADIO_APNWBEACONWHENSNR D 5 RELATIONAL DATA – Y 8 – – – – NO +AP_ID+NW_ID+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_NWBEACONWHENSNR D 4 RELATIONAL DATA – Y 9 – – – – NO +NW_ID+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_EYENWBEACONWHENSNR D 5 RELATIONAL DATA – Y 10 – – – – NO +EYE_ID+NW_ID+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_APBEACONWHENNWCH D 5 RELATIONAL DATA – Y 11 – – – – NO +AP_ID+BEACON_OK+WHEN+NW_ID+CHANNEL
CARAT7 I_SCAN_RADIO_NWBEACONWHENCH D 4 RELATIONAL DATA – Y 12 – – – – NO +NW_ID+BEACON_OK+WHEN+CHANNEL
CARAT7 I_SCAN_RADIO_EYENWBEACONWHENCH D 5 RELATIONAL DATA – Y 13 – – – – NO +EYE_ID+NW_ID+BEACON_OK+WHEN+CHANNEL
CARAT7 I_SCAN_RADIO_APNWWHENBEACON D 4 RELATIONAL DATA – Y 14 – – – – NO +AP_ID+NW_ID+WHEN+BEACON_OK
CARAT7 I_SCAN_RADIO_EYENWWHENBEACONAP D 5 RELATIONAL DATA – Y 15 – – – – NO +EYE_ID+NW_ID+WHEN+BEACON_OK+AP_ID
CARAT7 I_SCAN_RADIO_WHENEYESIGHTCHANTAP D 7 RELATIONAL DATA – Y 16 – – – – NO +WHEN+EYE_ID+SIGNAL_LEVEL_APEYE+HT_MODE+CHANNEL+ANT_ID+AP_ID
CARAT7 I_SCAN_RADIO_APEYEANTBEACONWHENSNR D 6 RELATIONAL DATA – Y 17 – – – – NO +AP_ID+EYE_ID+ANT_ID+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_EYEANTBEACONWHENSNR D 5 RELATIONAL DATA – Y 18 – – – – NO +EYE_ID+ANT_ID+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_EYECHBEACONWHENSNR D 5 RELATIONAL DATA – Y 19 – – – – NO +EYE_ID+CHANNEL+BEACON_OK+WHEN+SNR_APEYE
CARAT7 I_SCAN_RADIO_APWHENNWBEACON D 4 RELATIONAL DATA – Y 20 – – – – NO +AP_ID+WHEN+NW_ID+BEACON_OK
CARAT7 I_SCAN_RADIO_CH_ANTID_APID_DBI3 D 4 RELATIONAL DATA – Y 21 – – – – NO +CHANNEL+ANT_ID+WHEN+AP_ID
CARAT7 I_SCAN_RADIO_EID_ANTID_APID_CH_DBI3 D 4 RELATIONAL DATA – Y 22 – – – – NO +EYE_ID+ANT_ID+CHANNEL+AP_ID
CARAT7 I_SCAN_RADIO_EYEWHEN D 2 RELATIONAL DATA – Y 23 – – – – NO +EYE_ID-WHEN
- Get additional index usage data from tcbstats.
Index usage analysis from tcbstats:
TCB Index Stats:
Address TableName IID PartID EmpPgDel RootSplits BndrySplts PseuEmptPg EmPgMkdUsd Scans IxOnlyScns KeyUpdates InclUpdats NonBndSpts
PgAllocs Merges PseuDels DelClean IntNodSpl
0x00007F99D34C6EE0 SCAN_RADIO 25 n/a 0 0 9 0 0 550 328 0 0 134928 136332 0 0 0
1395
0x00007F99D34C6EE0 SCAN_RADIO 24 n/a 0 0 0 0 0 66 66 0 0 133191 133986 0 0 0
795
0x00007F99D34C6EE0 SCAN_RADIO 23 n/a 0 0 39 0 0 215 204 0 0 243578 247262 0 0 0
3645
0x00007F99D34C6EE0 SCAN_RADIO 22 n/a 0 0 31 0 0 120 120 0 0 241152 245526 0 0 0
4343
0x00007F99D34C6EE0 SCAN_RADIO 21 n/a 0 0 102 0 0 16 0 0 0 216808 220810 0 0 0
3900
0x00007F99D34C6EE0 SCAN_RADIO 20 n/a 0 1 68 0 0 0 0 0 0 518080 526974 0 0 0
8825
0x00007F99D34C6EE0 SCAN_RADIO 19 n/a 0 1 13962 0 0 310 310 0 0 427545 447655 0 0 0
6147
0x00007F99D34C6EE0 SCAN_RADIO 18 n/a 0 0 173 0 0 2 2 0 0 219850 223930 0 0 0
3907
0x00007F99D34C6EE0 SCAN_RADIO 17 n/a 0 0 39 0 0 6 6 0 0 243637 247319 0 0 0
3643
0x00007F99D34C6EE0 SCAN_RADIO 16 n/a 0 0 193 0 0 0 0 0 0 136991 139493 0 0 0
2309
0x00007F99D34C6EE0 SCAN_RADIO 15 n/a 0 0 364 0 0 128 128 0 0 134664 137137 0 0 0
2109
0x00007F99D34C6EE0 SCAN_RADIO 14 n/a 0 0 52 0 0 90 90 0 0 257142 261392 0 0 0
4198
0x00007F99D34C6EE0 SCAN_RADIO 13 n/a 0 0 214 0 0 118 118 0 0 179502 182811 0 0 0
3095
0x00007F99D34C6EE0 SCAN_RADIO 12 n/a 0 0 527 0 0 0 0 0 0 169237 172449 0 0 0
2685
0x00007F99D34C6EE0 SCAN_RADIO 11 n/a 0 1 54 0 0 0 0 0 0 438870 446363 0 0 0
7438
0x00007F99D34C6EE0 SCAN_RADIO 10 n/a 0 1 54 0 0 0 0 0 0 438871 446405 0 0 0
7479
0x00007F99D34C6EE0 SCAN_RADIO 9 n/a 0 0 507 0 0 0 0 0 0 167238 170397 0 0 0
2652
0x00007F99D34C6EE0 SCAN_RADIO 8 n/a 0 1 52 0 0 1 1 0 0 432856 440264 0 0 0
7355
0x00007F99D34C6EE0 SCAN_RADIO 7 n/a 0 0 191 0 0 4 4 0 0 175999 179221 0 0 0
3031
0x00007F99D34C6EE0 SCAN_RADIO 6 n/a 0 1 50 0 0 0 0 0 0 432808 440203 0 0 0
7344
0x00007F99D34C6EE0 SCAN_RADIO 5 n/a 0 0 38 0 0 1441 1398 0 0 244424 248014 0 0 0
3552
0x00007F99D34C6EE0 SCAN_RADIO 4 n/a 0 0 533 0 0 25 14 0 0 326689 332578 0 0 0
5356
0x00007F99D34C6EE0 SCAN_RADIO 3 n/a 0 0 1 0 0 2737 2737 0 0 133980 134785 0 0 0
804
0x00007F99D34C6EE0 SCAN_RADIO 2 n/a 0 1 292 0 0 25 25 0 0 399343 406545 0 0 0
6909
0x00007F99D34C6EE0 SCAN_RADIO 1 n/a 0 0 66704 0 0 517 500 0 0 26401 93604 0 0 0
499
0x00007F99D3AD61E0 SCAN_MANAGED 25 n/a 0 0 28 0 0 1431 1431 0 0 38589 39298 0 0
- 681
- Develop recommended action based on all above analysis and recommend indexes to be dropped to application developers. Get feedback from developers on indexes in question.
- Drop indexes and monitor post drop to ensure no negative impacts.
GOTO THE NEXT STATEMENT and REPEAT UNTIL Tuning Complete or return not beneficial.
