So, you want to run a big SQL Server database and maintain high performance? Start by sizing up—the database files, that is! Pre-sizing the database files appropriately will help ENOVIA V6 perform at its best when it runs on SQL Server.
When you create a database for the first time, we recommend that you create the files in a contiguous space that has been defragmented. To make sure that the database files are optimized for your SELECT statements, do the following three things:
· Create the files in a contiguous space that has been defragmented.
· Make all the database files the same size.
· Make the files as large as you anticipate you will ever need.
To defragment the hard disks, you can use a disk utility or the Disk Defragmenter included in the Windows Server operating system. But, be careful if there are already database files on the drive, because the disk defragmentation process will move pieces of your database files around and cause internal fragmentation of the files. Note that you'll have to rebuild your indexes after you use a disk defragmentation tool on your drive.
If you have more than one file in your filegroup, make all the files in the filegroup the same size. Doing this changes SQL Server's extent allocation strategy and creates the whole extent in a single file. SQL Server allocates space sequentially in extents, or units, of 64 KB. It uses a "proportional fill" strategy—this means that it tries to keep all of your database files filled to the same percentage.
If all of your database files are the same size, SQL Server will allocate 64 KB in the first file, then move to a second file and allocate 64 KB, and so on until it wraps back around to the first file, where it begins these allocations again. This type of allocation provides the best overall performance. However, if you have files that differ in size, SQL Server will try to maintain the same percentage of empty space in each file by allocating extra extents to the larger files. This uneven file allocation may put all the most recent data in one large file, causing a "hot spot" for the retrieval of that data. You don't want this.
To determine how large your database needs to be, refer to your ENOVIA V6 documentation.