Using SQL Server Profiler to Tune Your Applications

Two weeks ago, I explained the importance of SQL Server Profiler. In case you missed that article, I said you can't understand how to fully tune your applications unless you analyze them with SQL Profiler (or SQL Trace for 6.5 systems). I'm revisiting SQL Profiler to answer a few of the most common questions sent by readers.

Q: Do you have to be a member of the sysadmin role to run SQL Profiler?

You don't have to be a member of the sysadmin role to run SQL Profiler. The GUI calls a series of extended stored procedures (sps) prefixed with "xp_trace." You can give others the ability to run SQL Profiler by granting execute permissions to the appropriate roles. (Of course, good DBAs never assign permissions to individual users. It's best to assign permissions to roles even if the role contains only one user to start.)

Q: Can a user run the Index Analysis Wizard from Query Analyzer without being a member of the sysadmin role?

Unfortunately, I'm not aware of any trick to let someone run the Index Analysis Wizard from Query Analyzer without being a member of the sysadmin role. I've suggested to Microsoft developers that they add this functionality to future SQL Server releases, but I'm not aware of any good options for now. Please let me know whether you've found a workaround so I can share it with the rest of the SQL Server community.

Q: I see a lot of 'sp_cursorfetch' calls when running SQL Profiler. What do they mean?

Understanding this topic is one of the most important steps you can take to speed up sluggish SQL Server applications. I can't do this conversation justice in this small space, but here's a quick explanation.

I'm going to make some gross oversimplifications as I introduce this topic. Please save the flame mail if you're a hard core ADO, ODBC, or OLE-DB jock. Let's pretend your ADO application needs to open a record set on the EMPLOYEE table to process payroll. Logically, all ADO connections manage their record sets using cursors. The question is whether the application will use client-side or server-side API cursors. If our application uses client-side cursors, SQL Profiler will show traffic between the client and server as follows:

 "select * from EMPLOYEE"

Many people call this a fire-hose cursor because SQL Server pumps back all rows from the server in one result set. Server-side API cursors operate differently. API cursors store their statement and result sets on the server--sp_cursorfetch calls are made to return rows to the client application. Assuming the EMPLOYEE table has 1000 rows, client-side cursors will send one T-SQL statement and receive one result set containing 1000 rows. By default, ADO sp_cursorfetch returns one row from the server each time the application calls it. In our example, server-side API cursors would issue 1000 sp_cursorfetch calls, each retrieving one row, to process all the EMPLOYEE data. You don't need to be a rocket scientist to recognize that it's generally more efficient for SQL Server to process one query than 1000 queries to return the same amount of information to the client. I'm not suggesting server-side API cursors are always inefficient. API cursors are an excellent tool when used properly, but the default row fetch size of 1 is rarely a good choice for large record sets. It's a little like running a race with your feet tied together. You'll end up doing a lot of work, but you won't get anywhere very fast. Fixing your ADO applications might be pretty easy. The CacheSize property controls how many rows each sp_cursorfetch will return. Tweaking this parameter often has a huge benefit on application performance.

The two main ideas you should glean from this discussion are:

I hope I've whet your appetite enough that you'll want to learn more about this topic on your own. I can't begin to list all the information sources that might be helpful, but you'll find plenty of information on Microsoft's Web site.