Setting Up A SQL Server Profiler Template Tricia Robertson

Developing strong database design parameters for code development is one of the biggest challenges facing developers. To normalize or not to normalize, determining proper indexing of tables or creating the most efficient stored procedures, triggers and functions possible make designing good code an ever-going challenge.

SQL Server 2000 provides a Profiler tool that is useful for diagnosing database design problems and developing optimized database performance. In order to make this tool as a serious weapon in your development arsenal, set up a basic template for a trace that will be the starting point for assessing problems or determining database performance when implementing new code bases.

From the Profiler File menu, select New Trace. Choose your database and connection attributes. Now you are ready to select the Trace Properties, these are the events that will be shown in your resulting log file. You can choose to use an existing template, but once you get more comfortable with this tool, you will probably find that there are better selections for debugging complex stored procedures than there are for setting up the most efficient table indexes. Just because you are looking at table locking issues, doesn’t mean that every Locks class event will provide you with the best information. In this instance, you may want to select Lock:Acquired, Lock:Cancel and Lock: Released along with Execution Plan, SP:Starting and SP:Completed to determine whether or not the cause of the locking problem is code based. Tweaking the template for each analysis will probably be necessary. Also, don’t forget that it is possible to set up User configurable event classes within Profiler.

Once you’ve got a Trace set up, click run and make sure that the server is running the code/events you are trying to debug. To do this you may want to send the events from the application or you may be able to isolate the event by just kicking off a stored procedure from Query Analyzer. When done, click Stop and review the log file. Don’t be scared off by all the information in the log, dissect it to find exactly what is causing the problem or slowing down performance. The StartTime and Duration columns are a good place to start when looking at this information; an unusually high Duration value is a good indicator that something needs attention. Also the SPID column will tell you what is calling the event and it could be possible that there is SPID that is causing all of the problems and you didn’t even realize it was there. This is where development gets fun as you investigate the makeup of a database at a highly detailed level. Once you get a setup that you are content with, try running a trace while doing a load test.

Using the Profiler tool can save develop time and result in developing stronger code. If you’ve ever dreamed of losing yourself in a database, this is the tool to get you going!

• Posted In Engineering, QA, IT