Problem solve Get help with specific problems with your technologies, process and projects.

Sudden slowdowns in SQL Server: Database autosizing?

If a process causes a lot of activity in a table set up with automatic sizing, the system may grind to a crawl while it resizes the database.

When a database administrator creates a database in SQL Server, he can control how the database's filegroups (the physical files that contain the database) are sized initially and then resized later over time. The way resizing is handled can be done either by percentage or by number of megabytes. For instance, the default setting for tempdb (the database used by SQL Server for many temporary operations) is set to automatically grow by 10% every time more space is needed.

If a particular process causes a lot of activity in tempdb -- or any other table set up with automatic sizing -- the system may grind to a crawl while it resizes the database to fit. This will be further exacerbated by having tempdb on the same physical disk spindle as the other databases. (To be fair, it's not always possible to move it to another disk, but this does deserve mention.) The allocated space for a database's files can be seen by right clicking on the database in SQL Server Enterprise Manager, selecting Properties and then looking in the Data Files tab.

One way to find out if this is the culprit is to turn off autosizing for databases (tempdb in particular, if the slowdowns seem to happen during temporary-table operations). When you do this, be sure to set the database size decently high -- at least 100% more than would be used in normal operations. Otherwise, some queries may start failing.

Note that if you have queries that are generating inordinate amounts of tempdb activity, it may be a sign that the query itself isn't well-constructed and needs to be broken up into several more efficient sub-operations. It could also mean a lack of proper covering indexes for the tables being scanned.

Serdar Yegulalp is editor of The Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.