SQLINFO – We play with data and technology

This feature is dedicated to Preethy kammath a regular reader of my blog for last one year. Her questions are good enough to make me learn more about SQL Server.  Today she asked me about Database Performance Tuning Tips. Most of the IT industry people (who are not aware of DB Clearly) will say that performance Tuning is done after database is developed or database comes into Production. I heard from lot of people that just choose the right index and that will bring the performance. DB performance relies on various factors. It will vary on different scenarios. For e.g.: In some scenarios cursor will work better than while loop. However I will explain some major factors that will improve performance of the database. Here I am explaining only about OLTP Architecture. Performance Tuning Starts From design, development to Implementation and Maintenance. Here I am talking about only very common things.

• Database Analysis/ Design.
  • Design the database with DB Architect / DB Expert who have real time experience in the desired DB server. Because Each DB has different internal Architecture.
  • Proper Capacity Planning should be done before the design.
  • Database Normalization should be done during the design of the database. Database should be normalized to minimum Third level normal Form. IF you are going for Agile methodology there is different database design technique.
  • De normalization should be done if there are lots of joins needed for the queries for the gain of Performance.

Database Development (Writing Stored Proc’s, Trigger’s etc).

• Another critical Task lies here. Good SQL Code will improve much performance gain to the database. So Development DBA’s should be there for doing these tasks.

• Don’t allow the programmers to write code. This makes them over pressure and Project lack Good SQL Code. I am not against frontend developers, they will be learning more on the Application side. But for a Database Developer/ Development DBA they are forced to learn SQL development .So they will write good code.
• Don’t put index at the same while writing the SP’s or Queries. So Load Testing should be done.
• They can also test the database by putting load and choose right indexes.
• So experienced database developers is required in this area.

Database Implementation and Maintenance.

• Here comes the world of Production DBA.
  • Database Implementation is a key factor. So Keep DB Architect/DB Developer and Production DBA in this area.
  • Lot of Database settings will improve the Performance. So a production and DB architect should work closely in this area.
  • After the Implementation you have to maintain a database.
  • Regular database maintenance should be done like Statistics, Index de-fragmentation, Table Maintenance etc should be done regularly.
  • Database Backup and recovery should be done.
  • Load balancing (Replication, Logshipping), High availability (Mirroring, Replication, LogShipping, Clustering etc)  all these things need to be considered based on the business scenario.

Preethy ,I know this is the answer you are not expecting.But First you have understand the basic things.