A couple days ago I shared some of the results of a conversation I had with a group of DBA regarding what are the things we do that have the most immediate impact improving performance of SQL Server. We talked about optimizing TempDB and defragmenting data internally in SQL Server.
A couple of other things that came to the top were making sure the SQL Server Service has adequate resources of CPU and Memory. This is especially important when your SQL Server runs inside a virtual host operating system. Be sure the host has adequate physical hardware to support CPU and Memory being allocated to the SQL Service.
Today Dilip shares his thoughts in response to the earlier editorial:
Dear Ben,
Your articles are always a food for thought and for Re-Thinking. We all read them thoroughly and whenever possible try to share our thoughts on them.
Here, is what we feel-
1. One of the major ways to ascertain Data Consistency, Non-Orphenage and faster execution of query is having ABSOLUTE REFERENTIAL INTEGRITY.
In its absence, we will have sleepless nights and no explanation that can be given to end user for wrong results.
2. As regards, Query Optimization, doing things at Database Level as a DBA come at a much later stage.
These acts only add sugar to milk to make it more sweet. But, the real thing lies in defining an extremely well structured database design of your application system. This includes well defined file groups, definition of primary keys, definition of Referential integrity, definition of Indexes, very few but needy triggers (to be used rarely but still..), prior analysis of data mobility and changing nature such as more or less stagnant, semi-dynamic and absolutely dynamic, data volumes, proper lengths of keys – again numeric keys and alpha-numeric keys. So many such things matter first.
3. On DBA side well known Top Optimization techniques would include-
- Organization of file groups on multiple physical disk drives
- Pre-allocation of disk space to each file group, based on data volume analysis and its projected trends (No need to go to OS again and again)
- Separation of Log File onto another disk
- Periodic re-building of indexes
- CPU Core based but limited to 2-4 TempDB physical files.
- Defining Temporary Tables as base tables with Primary keys instead of creating them in TempDB (we have cut down time from 6 hours to 9 minutes) by using this technique and then periodically truncating these temporary (base) tables.
- if economically possible, then opt for higher main memory and 64 bit architecture.
This is how we have been working and will continue to work. In memory database will only further enhance the performance but a bad database design will still defeat the purpose.
Would you like to add your top performance tuning ideas? Add your ideas here online, or simply send an email to btaylor@sswug.org.
Cheers,
Ben