Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 10. Tracing SQL Server with SQL Trace and Profiler

WHAT'S IN THIS CHAPTER

• An overview of why tracing is important

• Deep look into the Architecture of SQL Trace

• Comprehensive coverage of the Tracing views, functions, and stored procedures

• An extended analysis of Security and SQL Trace

• Profiler, Profiler, and more Profiler

Profiler is a GUI; SQL Trace is the juice. That's the message for this chapter. SQL Trace is the architecture upon which server-side tracing and Profiler are built. Those who know and understand how SQL Trace works use server-side tracing to minimize the impact on their production servers. Profiler is a very useful tool and is a great option for ad-hoc performance analysis. However, it does tend to get used in situations where it should not be used.

In this chapter you will learn all about SQL Trace. Once you understand its architecture you'll come to appreciate the subtleties between server-side tracing and Profiler. Both methods have their advantages and disadvantages as you will soon see.

This chapter is going to take a deep dive into the guts of SQL Trace. You will learn all about its strengths and weaknesses. You will also learn much about security issues that arise from Profiler traces. The objective is for you to really understand the best ways to use both Profiler and server-side tracing so that ultimately you also understand your SQL Server.

TRACING 101

Before diving headfirst into the guts of SQL Trace it's probably worth stepping back and asking some fundamental ...