SQL Server – Offline Database Management

There are several methods to audit database transactions, but they all affect performance more or less

SQL Server Change Tracking and SQL Server Change Data Capture don't show who, when, and how executed the transactions. On the other hand, SQL Server Change Data Capture shows the old and new values for the UPDATE statements. Here is a useful set of comparison notes: SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC) and Comparing Change Data Capture and Change Tracking

SQL Server Auditing shows who, when, and how, but doesn't show the old and new values for the UPDATE statements

The methods that read the database transaction log files don't add overhead, as there is not additional change capturing. Besides fn_dblog, that can return results not easy to understand, there are third party tools, such as ApexSQL Log

There are two more auditing tools from ApexSQL - ApexSQL Audit which uses triggers, so can impact performance on a high transaction database, and ApexSQL Comply that uses SQL traces

SQL Server database auditing techniques

Disclaimer: I work for ApexSQL as a Support Engineer

To answer your questions in order:

• You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not specific files.

• For the most part, no, you don't need to worry about physical fragmentation of your files if you are using a SAN (unless the SAN is dedicated to this one purpose). Due to how most SANs are used, your IO is going to be random; which is fine since you should have LOTS of spindles to spread the load out.

• Larger physical database files would prevent fragmentation, but again, you shouldn't worry about that if you are on a SAN. The more important thing to make sure of is that when the file grows, it grows at a set size (rather than percentage) and that size is sufficient to handle a good amount of growth. You would want to grow the file to what you estimate the required size will be in X days and THEN defragment the volume.

This link - Brent Ozar goes into more detail and should include some more information you can use to either prove or disprove that the SAN is the reason for your bottleneck.