Ways to Imporove SQL Server Peroformance - Part 1

Hi Friends,

As i have promised that i will post some tips related to SQL server peroformance, so here they are...

1. Normalizing:

Normalize your table’s schema in such a way that all tables are reduced in columns and are related to other table with some reference.

That will improve performance while you are fetching data from tables that will also reduce the fetching of redundant data.



2. Define Primary and Foreign keys:

Make relation of your table in such a way that you can access any combination of data from various tables just by referencing keys.



3. Choose the most appropriate data types.

This is the main issue when actual data will be stored on local disk, because if you have given some inappropriate data type then it will consume more space than actually needed. That will degrade the disk IO performance.



4. Make Index:

Try to make Index on such columns which are frequently used in searching operations, so it will improve our query performance.

Do not make more indexes on one table that it will degrade the performance, because when table is to be updated by updating or inserting new data, indexes also will be updated, so try to make fewer indexes on table.



5. Return Values:

Return only those columns and rows which are actually needed for requirement. Do not try to fetch all rows and columns which are not needed, because it will slow down the fetching as well as make the high IO operations.



6. Avoid Expensive operations:

Try to avoid expensive operations such as “LIKE”, because we are normally using “LIKE %abc%” wildcard entries…and it will require table scan and it will make very slow response of query result.



7. Avoid Explicit or Implicit functions in WHERE Clause:

The optimizer cannot always select an index by using columns in a WHERE clause that are inside functions. Columns in a WHERE clause are seen as an expression rather than a column. Therefore, the columns are not used in the execution plan optimization.



EX: do not use where clause like this,

SELECT OrderID FROM NorthWind.dbo.Orders WHERE DATEADD(day, 15, OrderDate) = '07/23/1996'



Instead, we can use like,

SELECT OrderID FROM NorthWind.dbo.Orders WHERE OrderDate = DATEADD(day, -15, '07/23/1996')



Guys, i will post second part of this series very soon, so keep watching this BLOG :)