Systems Engineering and RDBMS

Archive for March 11th, 2007

Enhancements in SQL Server 2005 Profiler

Posted by decipherinfosys on March 11, 2007

There are several important enhancements introduced in SQL Server 2005 version of profiler which make troubleshooting and performance tuning a lot simpler.

1) One of them is the ability to analyze execution of MDX (Multidimensional Expressions) Analysis Services statements.  This is a big thing since previously troubleshooting issues in the BI applications used to be too painful.  Similarly, you can monitor the operation of SQL Server Integration Services (SSIS) which is a replacement for DTS in SQL 2000.  By having these capabilities, you can use these traces to debug any problems you have in these additional components of SQL Server.

2) Performance Monitor correlation works with profiler so there is a clear representation of what was going on in the system when a particular statement shows high IO or CPU costs. We had covered the perfmon co-relation before in a previous blog post here.

3) Profiler now allows you to save the trace file as XML. A traced ShowPlan result can be saved as XML and then loaded into Management Studio for analysis.

4) Profiler also integrates with the new Database Tuning Advisor, which replaces the Index Tuning Wizard.

5) SQL Server Profiler also has a visual representation of the deadlock sessions that yields a lot more information than the cryptic deadlock output in SQL Server 2000 that was obtained by turning trace flags on. We covered that in a previous blog post.

6) Permission to run the utility is no longer limited to members of SysAdmin fixed server role – this is a welcome change since now DBAs can give permissions to developers without comprimising any security.
7) Aggregated views allow grouping recorded events based on arbitrarily selected criteria.

8 ) Events of a specific type can be extracted and stored as corresponding file types (e.g. T-SQL statements as SQL files, or MDX statements as XML files). Similarly, Showplan and Deadlock trace files can be saved in XML format, and subsequently loaded for analysis in SQL Server Management Studio, which significantly simplifies troubleshooting.

9) Other improvements: A new selection UI with new events and data columns (we will cover those new enteries in one of the future blog posts), ability to extract out events and modify a paused trace.

Posted in SQL Server | 1 Comment »