SQL Server Database Tuning

Tuning your SQL Server !!! Well, I've already heard about it because in my old-office was a problem with the SQL Server. I was using SQL Server 2005 SP2. The query was so long to be executed. And At the first time, i had no idea to fix it. But i googgled in the internet and find something that tuning my SQL Server. I will write down the tuning list, If you have any idea please add it.

a. Reside your TempDB and your User Database.
Why ? Because TempDB is used for temporary Data. So it needs more I/O to improve the performance. If your User Database puts on the same drive with TempDB, The I/O will getting busy, cause they need to serve TempDB Database and your database.

b. Create Filegroup that you need
Creating filegroup as many as you need. Don't create only .mdf and .ldf file. Because if your database is getting bigger than now, the I/O can be stuck. So, you'd better create a several filegroups. it will help your performance. The best practice is build a filegroup based on your processor. if your processor is 4 and you can build 4 datafile at one filegroup.

c. Use Autogrow by Mb, Not Percentage
It's important. Because if you use Mb to growth your database, it's constant. But if you use percentage, it's flexibel. The Problem is, again and again if your database is getting bigger and bigger, Let say 20GB and your Autogrow percentage is 10%. The SQL Engine will growth your database about 2GB. it's need more time. But if you use Mb, Let say 100MB, it's always constant growth - always 100MB- and it needs less time than percentage

d. Create the proper Index
Index is the important part on your database. You can use index to improve your search data but it will hurt when your database is doing Insert and Update. So, Create the proper index, not just create index. I've read some articles, When your indexed columns is less than 5% data, so you need create the index, but more than that, i suggest you to remove the index. Index also needs at column that become a primary in tables. Because primary key is need to join tables

e. Create your query efficiently
Well, it's too difficult to be explained. But you can use execution plan to find the query isn't efficient to be executed. Let say, Don't use '*' in your select query. and the others. Don't use cursor oftenly. Cursor needs more processor and memory to allocate it. So, your processor will busy. But don't forget, sometimes cursor is very useful

Ok, maybe it's only that i know. I hope it would be useful..