Debugging with SQL Profiler

One additional tool that you can use to debug Nav is the SQL Profiler. This comes with most SQL installations. With it you can see all the SQL commands that Nav runs. The only issue with using it right away is that you get a lot of commands that done mean a whole lot. To fix this you can create a custom template.

Untitled

When you go make a new template for Nav copy the standard one so you have somewhere to start from. Now you are going to want to exclude a lot of commands. To do this go to the Events Selection tab.

You can un-check all the Security Audit events, these are mainly for when a user first authenticates with the SQL server. You will also want to add the Cache Hit Stored Procedure event. Nav caches and stores alot of the commands before they are ran. This way you can see what queries are ran multiple times.

Now if you use with filter the way it is now. You will see a lot of queries that pop up about once ever second that don’t mean a whole lot. To remove these you will want to copy out a snippet of the TextData they display, as well as what event class they are from.

To remove them edit your template, find the event class in the list, then select Column Filters. Find TextData, or what ever column you want to filter on and add a line that matches the lines you want to get rid of. Something along the lines of “sp_execute 10” is usually enough. If you find your filter takes out to much information you can always make your filter more detailed to take out less lines.

Untitled2

With your filter all set up you can easily see what Nav is doing behind the scenes.  This might not help to much with day to day debugging, but if you find performance problems or other SQL related issues. Looking at the SQL Profiler can be a big help.

Leave a Comment