Wednesday, December 4, 2013

Database Optimization : My Practices versus Best Practice

    "Best Practice" is more than a buzzword. It gets thrown around like one. You can google it but my take on best practice is its the actions that under normal circumstances will allow for best performance and disaster recovery preparedness. We should all aspire to build our environments in normal circumstances and be prepared for every possible setback that could or will eventually occur.
    When it comes to SQL Server normal circumstances can be very expensive and a lot of companies aren't on-board with these costs. We are going to try and imagine that isn't a concern . It may mean however you have to use judgement if following these practices.
      The first thing I look at is file configuration. It is my professional observation that sectioning Best practice says the system , temp, data and log files should all have separate drives. These drives should be separate from the O.S. Drive as well. If you cant have individual drives your priority should be to place the tempdb files on their own drives. The excessive writes caused by many applications create reason to isolate the I/O  requirements for these files.
      Second the data and the log files should be separated to the own drives. I believe the files should have no less than 15% unallocated space in them. So you have room on the drives for when the files fill and grow . When that occurs you should be able to manually grow the file to get the file back to an optimal unallocated percentage.
     I prefer to use multiple data files on any database over 10GB. This isnt really my rule of thumb. Its just what I have done in the past dealing with databases of all sizes. There are some thoughts about the number of files being relative to the number of C.P.U.. I do  follow this with regard to the tempdb, but I dont  follow this with database files. I factor in how many databases are on the server. I consider how which databases are more active then others. I believe the point of matching C.P.U. to file count by a ratio is to better manage the processes.    If a query needs to read data from the file it needs a C.P.U. to do some of that work.
   You may want to consider maintainability when you decide how many data files to make. It is easy to get carried away cutting a large database into many small pieces. If you need to do regular restores of a database with a great number of files it is more tedious than a database of only a few files.
   Some people might even be questioning the need to break the database into multiple files.  I take the stance that SQL Server needs how to properly identify where its data is located. If give it smaller files to find the data in you are intuitive lower the I/O needed to complete your queries.


No comments:

Post a Comment