This time, Lightspeed System's TTC seems to be the culprit. It has a nifty little system that sets autoshrink = true for all its databases, and then it gets terribly upset if the database file fills up. Of course, if it wasn't autoshrinking, then it wouldn't need to autogrow, and it wouldn't get its knickers in a twist because it couldn't autogrow on the terms of SQL's default, which is an autogrow of 10%.
When autoshrink = true, SQL will try to shrink all database files with more than 25% free space. This creates a performance hit for the CPU: the default setting for SQL is to set autoshrink = false (except on the Desktop Edition, where the default setting for SQL is autoshrink = true).
When autogrow = true, SQL will try to grow the database file while running an INSERT. The query may well time out, the autogrow will fail, and the database file will fill up. The default for SQL is to set autogrow = true, with a growth of 10%.
Both autogrow and autoshrink cause problems with file fragmentation and index fragmentation. It is vastly more healthy to set the initial database file size to the anticipated database file size, and then leave it well alone. There will be less load on the CPU, neither files nor indexes nor pages will fragment and the server will run more efficiently. It's like the difference between ironing a shirt on an ironing board, unimpeded, and ironing a shirt on a folded up towel on one corner of your work desk. Yes, it can be done, but no, we really don't want to as it takes longer and is vastly more exhausting.
Why Lightspeed Systems seems to think that Total Traffic Control's databases should be set to autoshrink is something I haven't yet established. My Google-fu is not strong today, so if you do know the answer, I would love to hear it.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment