Set your database size initially instead of allowing it to grow automatically. To minimize disk reads and writes, you may create the log file and tempdb into separate devices from the data. You can utilize RAID configuration with multiple disk controllers if the database performs large data warehouse operations. Have an optimal memory for your server and perform index fragmentation as and when needed. You can go with the automatic database shrink option to manage unwanted space. However, it is recommended that you use default server configuration for your application.

Some of the common mistakes that are usually noticed and should be avoided include:

·         Usage of GUIDs at times wherever not necessary and using GUIDs as primary key.

·         Having a clustered index GUID primary key makes a row bigger and it also degrades the performance as it would make every non-clustered index bigger.

·         Not being rational on the usage of data types, for example usage of larger data types where smaller data types would suffice the purpose.

·         No proper attention to remove missing or unused indexes. Poor decision making capabilities to identify the usage of clustered and non-clustered indexes.

·         Un-utilized columns and rows which may cause excessive data density.

·         Execution plan is ignored while writing queries. Each query adds up to extra time for processing.