Databases and Performance We Should Consider

I’m sure most of us are happy with wizards which Microsoft provide in SQL and forget what is happening behind the wizards. This is one example for our ignorance in SQL 2005. Normally we right-click on the database node in Enterprise Manager, click New Database, type the database name and click OK to create new database in 2005 Version. But we forget about options from the wizard and ignore what is happening behind the wizard dialog-box. Because we satisfy our needs of creating database and the wizard create database for us very easily within 3 clicks. If you are a little bit take care of your server hard-disk space and have knowledge to put your database file on the other disk drive, you’ll change the default path of the database in the wizard. That’s the most we do. The rest we ignore and let SQL Server handle itself.

When our database file is bigger and bigger, and the performance is drop, I’m sure we’ll think of memory and hard-disk space first. Some more, we’ll blame on the windows configuration. Actually, we forgot what we ignore at the stage of database creation. There’re some points we should consider for our database performance which we ignore or forgot. Here are some points we should consider for our database performance.

The first thing is that if we put our database files on the same partition which resides windows system files, the database performance will slow. You’ll wonder why SQL database wizard set default location to that partition. Because the server application cannot know you disk drives partitions and allocation. So always show current installed folder as default location. And you’ll wonder why the performance is slow if the database is exists on the same partition with windows. Because Windows is using its system files from that partition, put page files on that partition and temporary files are also put on that partition. So that partition is the busiest partition on your system and you add more load on that partition to read and write your database. So your database will definitely slow in transaction. And the worst thing is that if you reinstall Windows and you forgot to backup your database, that’s it. Therefore, we should place our database on the other partition or if possible on the other hard-drive.

Another thing is to set the file size AUTOGROWTH property. You should consider your data read and write amount. If you set your file AUTOGROWTH property as by 1 MB default and your data write amount is more than 2 MB, your transaction will need to wait for SQL Server to auto growth to finish your one insert transaction. So set AUTOGROWTH property to cover your data read and write amout.

If possible partition your database files based on the period and put most of the historical data on the secondary file group. So that your reports will not disturb your data transaction on the primary partition.

And if you can effort RAID system and your server support 2 or 3 types of RAID system, use the RAID feature to boost your SQL performance. Use RAID 1 for your database log file(s) which is important for database all the time. And use RAID 0 or 5 for your database file. Especially RAID 0 is preferable if you don’t mind fault tolerance. So RAID 10 or RAID 01 is good for your SQL Server performance.

The above are some points for database performance consideration before we start creating our database(s).

You also can find some performance tunning topics on SQL Server Central