Module: CCA, ODS, OCS
Related Product: MS SQL Server
It is common to have customization in several Genesys components, such as reporting (ODS or CCA), and outbound (OCS).
If MS SQL Server is used, then consultants or engineers must monitor tempdb space usage to determine any poorly design component, such as SSIS, database store procedure, trigger, or replication.
Although bouncing of MS SQL Server will free up the space, and shrinking the mdf file (can done online), finding the root cause and poorly design problem is the ultimate solution. This post will help to overcome temporary disk usage, but not to determine the root cause.
For Oracle, it is TEMP tablespace, and UNDOTBS tablespace. However, the nature of its usage is totally different than tempdb. If TEMP tablespace grow, then buffer cache (in RAM) needs to increase. If UNDOTBS increase, then review whether data volume is too big (potential bug if not datamart related), storage too slow (SQL queue up), CPU contention (again SQL queue up), or reduce UNDO_RETENTION (needs to assess the application requirement for the undo data).
For UNDO_RETENTION, Genesys application normally process very quick, and does not need more than a minute of retention. This parameter is normally tune up due to customization in ODS, CCA, and OCS
Following SQL will help to understand the usage of tempdb.
tempdb Analysis
Usage breakdown
SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
usr_obj_kb internal_obj_kb version_store_kb freespace_kb mixedextent_kb
---------- --------------- ---------------- ------------ --------------------
2048 604096 0 36232640 4416
Top 20 Usage by session
SELECT top 20 * FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC
session_id database_id user_objects_alloc_page_count user_objects_dealloc_page_count internal_objects_alloc_page_count internal_objects_dealloc_page_count
---------- ----------- ----------------------------- ------------------------------- --------------------------------- -----------------------------------
108 2 0 0 51072 51072
114 2 0 0 12480 12480
92 2 0 0 1016 0
124 2 39 37 0 0
94 2 0 0 16 0
143 2 16 7 0 0
136 2 13 8 0 0
55 2 12 6 0 0
85 2 12 6 0 0
84 2 11 0 0 0
134 2 10 6 0 0
97 2 9 0 0 0
138 2 9 9 0 0
126 2 8 5 0 0
137 2 8 8 0 0
98 2 7 6 0 0
112 2 7 6 0 0
74 2 6 1 0 0
93 2 6 0 0 0
129 2 6 6 0 0
Trace the session ID and determine the username. Genesys DAP and Genesys DB Server must configured with distinct username in order to determine the components affected. If a common username is used, then try to determine its originating IP address, or program name
Top 20 current space allocated
SELECT top 20 * FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC
session_id database_id user_objects_alloc_page_count user_objects_dealloc_page_count internal_objects_alloc_page_count internal_objects_dealloc_page_count
---------- ----------- ----------------------------- ------------------------------- --------------------------------- -----------------------------------
108 2 0 0 51072 51072
114 2 0 0 12480 12480
92 2 0 0 1016 0
124 2 39 37 0 0
94 2 0 0 16 0
143 2 16 7 0 0
136 2 13 8 0 0
55 2 12 6 0 0
85 2 12 6 0 0
84 2 11 0 0 0
134 2 10 6 0 0
97 2 9 0 0 0
138 2 9 9 0 0
126 2 8 5 0 0
137 2 8 8 0 0
98 2 7 6 0 0
112 2 7 6 0 0
74 2 6 1 0 0
93 2 6 0 0 0
129 2 6 6 0 0
Current active request
SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,
t2.statement_end_offset, t2.plan_handle
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id
AND (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC
session_id request_id task_alloc task_dealloc sql_handle statement_start_offset statement_end_offset plan_handle
---------- ----------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------
114 0 293632 53960 0x020000000BD83117633F7C85432DD122569DBCB102F7C1B6 0 -1 0x060007000BD83117B8A16B33000000000000000000000000
141 0 0 0 0x02000000ED9BCE2B47A4FA5065E6C3EE5C61717921937364 72 -1 0x06000700ED9BCE2BB8416F72000000000000000000000000
146 0 0 0 0x02000000F3399A2D68766038497DE81333B6EFE9EFA6D29F 0 -1 0x06000100F3399A2DB8613432000000000000000000000000
1 0 0 74968 NULL NULL NULL NULL
2 0 0 0 NULL NULL NULL NULL
3 0 0 0 NULL NULL NULL NULL
4 0 0 0 NULL NULL NULL NULL
6 0 0 0 NULL NULL NULL NULL
7 0 0 0 NULL NULL NULL NULL
8 0 0 0 NULL NULL NULL NULL
9 0 0 0 NULL NULL NULL NULL
10 0 0 0 NULL NULL NULL NULL
12 0 0 0 NULL NULL NULL NULL
13 0 0 0 NULL NULL NULL NULL
14 0 0 0 NULL NULL NULL NULL
15 0 0 0 NULL NULL NULL NULL
16 0 0 0 NULL NULL NULL NULL
17 0 0 0 NULL NULL NULL NULL
18 0 0 0 NULL NULL NULL NULL
19 0 0 0 NULL NULL NULL NULL
20 0 0 0 NULL NULL NULL NULL
21 0 0 0 NULL NULL NULL NULL
22 0 0 0 NULL NULL NULL NULL
23 0 0 0 NULL NULL NULL NULL
Trace the SQL handle to determine the SQL which used up the space
Versioning usage top 20
SELECT top 20 transaction_id, transaction_sequence_num, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC
transaction_id transaction_sequence_num elapsed_time_seconds
-------------- ------------------------ --------------------
2225005580 2662907 3
No comments:
Post a Comment