What these numbers represent. .


I have a very important question and I was wondering if someone from this 'support' blog could please look at the data I provided below and help me identify what the large numbers (such as 285,516)represent, because I have discovered that I have been searching for my book in all the wrong places.

18:10:34

I have a scenario where a table has 2.1 million records in it. The primary key is a composite key:
client_id char(5)transaction_id char(35)
This table has a large number of indexes (15) to support all the various reporting queries that have to take place. A typical insert into this table takes a 25-200 ms typically. The table has one trigger on it that updates another table when a certain column changes values, but that is an AFTER UPDATE trigger so it should not no impact on the insert since it isn't called.
Below is some sample timing information that shows the time to perform the insert observed by the client in milliseconds:
2008-01-12 17:32:49,777 time [63]
2008-01-12 17:34:04,389 time [34]
2008-01-12 17:34:05,638 time [129]
2008-01-12 17:34:47,892 time [49]
2008-01-12 17:34:56,669 time [223]
2008-01-12 17:36:52,226 time [16]
2008-01-12 17:38:22,423 time [391]
2008-01-12 17:38:47,038 time [746]
2008-01-12 17:44:23,453 time [168,240]
2008-01-12 17:44:23,979 time [67,022]
2008-01-12 17:44:24,558 time [235,738]
2008-01-12 17:44:24,709 time [178,876]
2008-01-12 17:44:24,756 time [81,065]
2008-01-12 17:44:25,023 time [285,516]
2008-01-12 17:44:25,061 time [83,065]
2008-01-12 17:44:25,549 time [181,647]
2008-01-12 17:45:32,396 time [188]
2008-01-12 17:45:38,917 time [270]

As you can see the timing is usually normal and then there will be a block of inserts that took a variety of time to complete. There are never any deadlock messages reported related to this table so I don't think it's a deadlock, but whenever I see the problem underway I would run the following query to see if a certain lock was having issues being acquired:

SELECT object_name(object_id), *
FROM sys.partitions
WHERE hobt_id in (
select resource_associated_entity_id
from sys.dm_tran_locks
where request_status <> 'GRANT'

This has not yielded anything meaningful. I thought that it might have something to do with synchronous statistics updating so I turned on the "update statistics asynchronously" on the database, but that did not change the problem. To be as helpful as possible I've included the output of a DBCC SHOWCONTIG:
DBCC SHOWCONTIG scanning 'client_transaction' table...
Table: 'client_transaction' (1434488189); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 645727
- Extents Scanned..............................: 81621
- Extent Switches..............................: 88886
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 90.81% [80716:88887]
- Logical Scan Fragmentation ..................: 2.42%
- Extent Scan Fragmentation ...................: 54.45%
- Avg. Bytes Free per Page.....................: 1611.5
- Avg. Page Density (full).....................: 80.09%
During these very slow inserts were not seeing anything else about the application slowing down. Everything else feels normal.

Does anyone have any ideas of how to go about troubleshooting this?


Potter's Lamb
www.amazon.co.uk/dp/141964968X
Saturday, March 22, 2008

[ reply to this topic ]   [ return to topic list ]

© 2024 Expat Software Back to Top