Good Content From SQLCAT Team

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.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.
Technorati Tags: ,

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s