Starting with SQL Server 2005, there were a number of new tools provided in the product which would help the SQL Server administrator have vital information within close reach.
These tools (hm, I should probably not call them ‘tools’, since most of them were far from being finished tools with proper UI and easy access) include:
- the DMVs
- the DTA
- the great Performance Dashboard reports
- the default trace
- and some more, which came with SQL 2008
In my opinion, one of the most underestimated assets for performance and security tuning is the Default trace.
The Default trace is a lightweight trace which is running by default on every newly installed SQL Server instance (SQL 2005 and up). It collects a set of data which is vital for the audit of the well-being of the SQL instance.
The data collected in the Default trace can be classified in the following categories:
- Database events
- Errors and warnings
- FullText events
- Object events
- Security audit events
- Server memory change events
Here are some drawbacks of the Default trace:
- impossible to change the default properties of the Default trace – it consists only of 5 files which are 20 Mb each
- the files roll over every time the instance is restarted
- data retention settings – there is no way to easily retain the trace data; once the oldest trace file is rolled over, the data is lost
- the files roll over at an unpredictable pace – depending on how busy the server is, and how many events are captured per unit of time
- there is no way to filter out certain events (scheduled index maintenance jobs, for example)
Despite the limitations, the Default trace is still a great entry point for performance and security audit.
Here is a detailed article explaining the details of the Default trace in SQL Server 2005 and 2008.
Here are some screenshots of the Default Trace Audit reports.
And here you can download the Default Trace Audit reports: DefaultTraceAudit (1986)