ChoirulAmri.org

DBA daily life is always hectic. They receive many issues ranging from network, performance, security, or application. I had collected many scripts and tools since couple of years ago. They are very useful for ad-hoc troubleshooting and diagnose the problem on the spot.

When I mentioned ad-hoc means the ability to locate and diagnose the problem quickly, without collecting data with SQL Profiler or Perfmon. I’m not saying that ad-hoc approach is better, but there are many situations when we just do not have enough time to collect the data while my users are screaming. The DMVs become handy tools for this purpose. It shipped since SQL Server 2005, and has been improved in 2008. I always has those DMVs in my shortcut folder, or in most cases in my thumb drive.

I had the opportunity to do demo on this topic at Singapore SQL User Group meeting last month. The response was fantastic, knowing that we have many free scripts to solve our daily problem. I promised to upload all presentation and sample codes, so here they are:

I blogged about backup compression here, and I mentioned that the main advantages are saving space, reduced backup and restore time. After discussion with some fellow MVPs, I have to revise my statement in previous post.

Yes, backup compression saves backup and restore time only if there are enough CPU and IO resources available. This is obvious because IO is the biggest bottleneck when reading/writing backup file. Thanks to Aaron and Andrew for remind me about this fact.

However, IO bottleneck is solvable if we use fast removable storage (eSATA for example) instead of internal hard disk. With this way, we can take full advantage of compression without affecting current machine performance.

The SQL Customer Advisory team did a very comprehensive benchmark on this. Below is some screen shoot I cut and paste from their blog post:

The complete post for benchmark by SQLCAT is here

http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx