July 31, 2015 at 10:39 am
Hi
I am thinking I know the basics of the problem here, but I want to make sure I know the details before trying to explain it to others.
We recently upgraded our third party application software to a new version. This version completely switched the Java Application Stack to a different vendor. After the upgrade we were told by the vendor that other customers who had upgraded were experiencing tempdb growth. I checked my monitoring and found we are beginning to experience the same.
The vendor claimed the only way to clear Tempdb was to reboot the SQL Server. They also don't really know what is happening because they are only looking at the disk space through the database properties, which I assume uses the flawed sp_spaceused sproc.
My monitoring uses the tempdb.sys.dm_db_file_space_usage dm so I am able to get a much more accurate view. I can see the internal object usage constantly climbing and not going back down at all.
I set up additional monitoring by using sys.dm_db_task_space_usage and can see the connections for this software have large allocation number with very little deallocation. It also looks like it opens connections and keeps them open but they will often be sleeping, which is consistent with the behavior of an application pool, from what I understand.
+------+-------------------------+-------------------------+-------------------------+--------------------------------------------------------------+-------------------------------------------+---------------------------------------------+
| spid | login_time | last_request_start_time | last_request_end_time | EventInfo | session_internal_objects_alloc_page_count | session_internal_objects_dealloc_page_count |
+------+-------------------------+-------------------------+-------------------------+--------------------------------------------------------------+-------------------------------------------+---------------------------------------------+
| 182 | 2015-07-30 08:09:36.667 | 2015-07-31 01:30:01.670 | 2015-07-31 01:30:01.670 | FETCH API_CURSOR000000000021C32E | 94496 | 72 |
| 74 | 2015-07-30 09:15:00.880 | 2015-07-31 01:30:01.103 | 2015-07-31 01:30:01.103 | FETCH API_CURSOR000000000021C32E | 80560 | 88 |
| 94 | 2015-07-30 10:11:47.270 | 2015-07-31 09:49:44.243 | 2015-07-31 09:49:44.243 | IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off | 71824 | 72056 |
| 57 | 2015-07-30 09:31:00.933 | 2015-07-31 01:30:01.760 | 2015-07-31 01:30:01.760 | FETCH API_CURSOR000000000000005B | 41648 | 8 |
| 289 | 2015-07-31 08:41:09.023 | 2015-07-31 09:50:00.943 | 2015-07-31 09:50:00.947 | FETCH API_CURSOR0000000000308955 | 40992 | 39944 |
| 209 | 2015-07-30 09:30:00.827 | 2015-07-31 01:30:03.770 | 2015-07-31 01:30:03.770 | FETCH API_CURSOR000000000021C32E | 40152 | 128 |
| 90 | 2015-07-30 07:56:40.960 | 2015-07-31 09:34:12.143 | 2015-07-31 09:34:12.147 | FETCH API_CURSOR0000000000000636 | 33992 | 64 |
| 67 | 2015-07-30 09:21:00.960 | 2015-07-31 09:05:51.200 | 2015-07-31 09:05:51.203 | FETCH API_CURSOR0000000000000636 | 23984 | 24 |
| 91 | 2015-07-30 07:56:41.730 | 2015-07-31 09:49:34.717 | 2015-07-31 09:49:34.717 | SELECT 1 | 23608 | 0 |
| 109 | 2015-07-30 12:48:32.603 | 2015-07-31 01:30:02.480 | 2015-07-31 01:30:02.480 | FETCH API_CURSOR000000000021C32E | 19184 | 0 |
| 64 | 2015-07-30 09:31:31.847 | 2015-07-31 01:30:04.513 | 2015-07-31 01:30:04.513 | FETCH API_CURSOR000000000021C32E | 18576 | 8 |
| 242 | 2015-07-31 01:30:59.950 | 2015-07-31 09:49:44.230 | 2015-07-31 09:49:44.230 | IF @@TRANCOUNT > 0 COMMIT TRAN | 15448 | 32 |
| 124 | 2015-07-30 07:57:05.883 | 2015-07-31 09:49:35.743 | 2015-07-31 09:49:35.743 | FETCH API_CURSOR000000000000004F | 9048 | 0 |
| 234 | 2015-07-31 01:30:32.983 | 2015-07-31 09:49:59.460 | 2015-07-31 09:49:59.460 | IF @@TRANCOUNT > 0 COMMIT TRAN | 8328 | 384 |
| 92 | 2015-07-31 01:30:59.750 | 2015-07-31 09:49:55.347 | 2015-07-31 09:49:55.347 | IF @@TRANCOUNT > 0 COMMIT TRAN | 7072 | 136 |
| 304 | 2015-07-31 09:40:08.403 | 2015-07-31 09:47:56.467 | 2015-07-31 09:47:56.467 | FETCH API_CURSOR00000000002C4C08 | 4392 | 3360 |
| 288 | 2015-07-31 09:15:03.253 | 2015-07-31 09:49:45.720 | 2015-07-31 09:49:45.720 | FETCH API_CURSOR0000000000000013 | 3784 | 3072 |
| 184 | 2015-07-30 08:09:37.423 | 2015-07-31 01:30:05.953 | 2015-07-31 01:30:05.953 | IF @@TRANCOUNT > 0 COMMIT TRAN | 3240 | 8 |
| 291 | 2015-07-31 09:41:13.850 | 2015-07-31 09:49:34.370 | 2015-07-31 09:49:34.370 | FETCH API_CURSOR00000000002DA77F | 2792 | 1760 |
+------+-------------------------+-------------------------+-------------------------+--------------------------------------------------------------+-------------------------------------------+---------------------------------------------+
It seems to me when the requests are ending the code stops but the connection stays open and the internal objects are not being deallocated. Can someone confirm this and help me to explain it? I know SQL but have very little knowledge of connection pooling.
Can this be corrected by adding a connection setting to their pools, something like SET XACT_ABORT = ON?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply