Performance issues That can be the result of an

improper  Database design

1) Normalization.

Data Normalization Reduces the data being stored on the physical disc. Normalized tables produce rapid, efficient joins, hence reduces Query time drastically, Normalizations makes it possible to create lesser indexes which makes the server more efficient. This will also reduce I/O issues, Memory issues, High CPU Usage issues because of lesser data being stored on the disk.

2) Query design to lower read times.

Queries Have to be designed in a way where there is the least load on the database server.
Making sure that every time there is a where clause for a column either we should have a clustered index nor a non-clustered index. Removal of functions inside a query reduces the query executions time and compensate by using computed columns by storing the computed values.

3) Table partitioning techniques.

If a table has 10 columns and only 5 out of ten columns are being use its better to portion the table into two with 5 column’s which are mostly read and other 5 least used into another table. This reduces I/O and increases efficiency, The same applies to row data too rows which will be used the most can be in one table and the ones read least can be into other table.

4) Proper Indexing of tables.

Indexing is a tricky situation. Its always advisable to create indexes where most of the where clause is applied, preferably a clustered index, and the rest if required should be a non-clustered index. Do not create indexes if deemed unnecessary.

5) Using temp tables or joins instead of cursors.

Temp tables are faster and does not lock tables hence reduces the Locks and Disk I/O which makes huge gains when performance is considered.

6) Improper maintenance schedule Design.

If maintenance activities are scheduled at high traffic times  and we perform a maintenance activity at that that time that will impact the performance of the server. Hence Maintenance should be scheduled to be done at the least usage time.

7) Improper isolation levels.

Improper isolation levels create lag in the query and improper data being displayed hence proper Isolations also should be implemented while designing the Database.

8) Inclusion of functions in the WHERE clause.

Instead of a simple selection of data, data will have to be computed if a function is involved. This drastically increase the query execution time. Hence its wise to design a query with the least amount functions. This can be achieved by using Temporary tables and computed columns.

9) No Separate Data and Log Files.

Rule no 1 while installation of SQL-server is to define separate drives for  Data and Log-Files. This reduces data contention, Space issues, File growth issues.

10) Improper capacity Planning.

Improper capacity planning depending on the expected transactions and data storage Cause not just perfomance but sometimes even policy and SLA related situations.Hence Capacity planning also could be come a reason for sudden bottlenecks popping up in time.

Continued to Next Page ------>>