If you haven’t used SQL Profiler in SQL Server 7.0, that’s a shame because SQL Profiler can shed light on what’s going on behind the scenes in an application. For example, many developers use ADO and Active Server Pages (ASP) to write Web applications that use SQL Server as the back-end database. Some developers also use ASP and put the database ADO code in Visual Basic (VB) components. As a forthcoming article in SQL Server Magazine by Bob Pfeiff will show, developers can dramatically improve how these types of applications work with the database engine by changing one ADO recordset property or command object. You would think that issuing a select statement such as

SELECT * FROM titles

would work in a simple manner when it hits the server. But what the developer doesn’t see is how ADO and SQL Server handle that statement. If the developer uses a cache size of 1, this statement might generate 20, 50, or more statements for SQL Server to execute. If you change the cache size to a higher value, SQL Server needs to execute only three or four statements.

You can use SQL Profiler to uncover many other aspects of how ADO and similar technologies handle SQL Server statements. To access SQL Profiler, select Profiler from the SQL Server 7.0 programs folder. Then you can start a trace by clicking the New Trace button on the toolbar and providing the name of the trace on the Property page. When you close the Property page for the new trace, the trace window will open and the trace will begin. The trace will track any command that hits SQL Server.

I find the Profiler most useful when I can use it to test a new application on a SQL Server system that no other applications are using. Then I can test the application by issuing queries in Query Analyzer and using ADO within the application. This procedure lets me see how SQL Server handles the executing statements.

Tools such as SQL Profiler and Query Analyzer are handy for finding performance problems. You can use these tools to analyze a poorly performing application and make it fly by tweaking certain parts of the code.