Friday, March 9, 2012

Experiencing time-outs with INSERT into Large SQL 2000 table

We have a very large SQL 2000 table here (150 million rows) and normally INSERTs happen with no issue. Occasionally, however, the performance is terrible for INSERTs and actually times out. Is there something going on with new the extents allocated for a very large table (i.e., is the number of extents allocated proportional to the size of the table?) .

Consulted offline with SQL Server MVP Kalen Delaney - her response as follows:

"As a file grows, the growth of the files is proportional to the size of the files. However, extents are always allocated to a table one at a time. The slow inserts might be due to page splits requiring a new extent, and there is no free space in any existing extents, and no free extents, so a file has to grow. If your growth amount is large, I have seen this time out."

|||This is correct, the database file was set to autogrow by 10% and is already at 15 GB, so adding 1.5 GB to the file timed out this INSERT. I was able to confirm by looking at the file modification date - it matched exactly with the time that the timeout occurred.

No comments:

Post a Comment