Search This Blog

2012-01-17

Genesys Database Analysis: MS SQL Server tempdb

Product: Genesys
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:

Blog Archive