LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

This post will demonstrate that there is a difference in performance if you don’t size your database file accordingly. It is a good practice to have your database sized correctly for the next 6 to 12 months, you don’t want your server wasting cycles with growing files all the time. Figure out how big your files are now, figure out how much they will grow in the next year and size your files accordingly, check back every month or so to see if your estimates were correct.

By default SQL Server will create databases wilt very small files when you create a database and you don’t specify the sizes. If you have people creating databases on your servers, consider adding a DDL trigger to notify you when a new DB is added so that you can talk to the database creator and size the files. You also can change the defaults on the server so that you don’t have the 10% growth either.

First let’s see what the difference is when we have a database where the files will have to grow versus one where the files are big enough for the data that will be inserted.

Here we are creating two databases, one with much bigger files than the other one

[/edit]
These two stored proc calls are just to verify that the files match with what we specified, you can use sp_helpdb to check the size of a database that you created when you don’t specify the file sizes

This query is just used so that the data is cached for the two inserts later on, this way the data doesn’t have to be fatched from disk for either inserts, you can discard the results after the query is done

On several machines I tested on, it takes half the time or less to insert the data in the bigger database compared to the smaller database. How about on your machine, do you see that the insert into the bigger database takes less than half the time it takes to insert into the smaller database?

Message
Autogrow of file ‘MyDB_Log’ in database ‘MyDB’ took 104381 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

See that, it took a long time, you don’t want to grow a one terabyte file by ten percent, that would be one hundred gigabytes, that is huge. Use something smaller and don’t use percent, the bigger the file gets the longer it will take to expand the file.

File placement
Separate the log files from the data files by placing them on separate hard drives. Placing the files on separate drives allows I/O activity to occur at the same time for both the data and log files. Instead of having huge files consider having smaller files in separate filegroups. Put different tables used in the same join queries in different filegroups as well. This will improve performance, because of parallel disk I/O searching for joined data.

Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. This will improve performance, because of parallel I/O if the files are located on different physical disks. Just remember that you can’t separate the clustered indexes from the base table, you can only do this for non clustered indexes. Of course people can get very creative, I have worked with a database once where each table was placed in its own filegroups, there were hundreds of files….what a mess

Test, test, test
Never ever blindly follow what you read on the internet, make sure that you test it out first on a QA server before promoting the changes to production!!

That is all for day one of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

2 Comments

One thing that might be interesting to you, when I ran your script to create TestSmaller I got:
“Msg 1803, Level 16, State 1, Line 2
The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.”