Microsoft’s Burzin Patel has a Top 10 List called Top 10 SQL Server 2008 Features for ISV Applications, that lists what he believes are the ten most valuable new features in SQL Server 2008 for independent software vendors (ISV). It is a pretty good list, although some of the features he includes may be outside of the control of an ISV when their application is running at a customer site. A good example of this is the “Optimize For Ad Hoc Workload” setting, which is set at the instance level. If a customer has multiple ISV applications running on a database server, their internal DBAs may not want to enable this setting themselves, and they may not let the ISV change it instance-wide either.
Microsoft’s Mike Ruthruff has a very detailed Technical Note entitled Diagnosing Transaction Log Performance Issues and Limits of the Log Manager, that has quite a bit of useful information on this subject. The note also supplies more ammunition for why you want to upgrade to SQL Server 2008 from SQL Server 2005, since 2008 has some higher limits for “in-flight” log I/O requests that can help performance.
A modified version of one of his queries is shown below. It lets you check how the transaction log file is doing for the current database without having to specify the logical name of the log file (which makes it easier to use on multiple databases).
-- Look for transaction log performance issues SELECT vfs.database_id, df.name, df.physical_name,vfs.file_id, ior.io_pending ,ior.io_handle, vfs.file_handle FROM sys.dm_io_pending_io_requests AS ior INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) AS vfs ON (vfs.file_handle = ior.io_handle) INNER JOIN sys.database_files AS df ON (df.file_id = vfs.file_id) WHERE df.file_id = 2 – File ID 2 will be the transaction log file, unless you have more than one log file.