Data Tune - Don't Wait For a Problem

Just like the human body, we should keep our databases tuned and running efficiently. Your doctor will recommend to you to monitor and manage your cholesterol, body fat, and general health. We shouldn't wait until we need a gastric band before we change our eating habits. It's the same with a database system. We shouldn't wait for a problem. Rather, we should continually monitor and tune our databases. 

How do we do this

• Continually monitor total CPU usage. If it's over 90% for sustained periods, there's little headroom and you should tune your expensive queries (and/or add more cpu resource). Note, expensive queries are the ones that use the most "total" CPU. So a query that runs 5 times per second using 0.5 seconds is 100 times more expensive than a query that runs once per hour using 90 seconds CPU.
• Continually monitor average read and write IO latencies. If average latency is over 15ms for more than a few minutes per day, then you have an emerging IO bottleneck. The solution is often to tune queries that incur lots of IO. These queries often only require better predicates, updated statistics, or an index to prevent a table scan. Note, if your queries are well tuned, the solution might be to increase RAM (SQL holds more in the buffer so needs less IO) or to upgrade the disk subsystem to support higher IO rates.
• Periodically enable SQL Profiler and find the most expensive SQL queries. It might surprise you how much you can take off the total system resources by tuning the top 10 SQL queries
• Look at your compression strategy. SQL Server likes compressed tables. They take less space in the data cache and require fewer IOs for a full/partial table scan. The CPU overhead in reading these tables is almost negligible. 
• Look at your fillfactor for large tables. If you are sequentially (by clustered key) loading these tables, the default of 10% is just wasting space on disk and data cache. Use fillfactor=100 for sequentially loaded tables.
• Etc.

If you ignore your database until there is a performance issue, it will be hard to fix. With a performance bottleneck, adding Profiler traces etc, will only make it more unstable, initially. So it will be hard to find the issue without further slowing down the system.

Continual tuning will also keep your system overhead to a minimum and you'll get payback from lower hardware and licensing costs. Note, as you add cpu (and to some extent RAM) to your server, your SQL Server licensing costs will increase too. So keep your database systems tuned and healthy. 

I am happy to discuss this topic in more detail as it applies to your systems.