Siusic Dot Com

Today I need to find out whether one of the SQL servers had performance problem because a monitoring tool developed in house was notifying us a store procedure call on a database server was timing out. In order to find out what was wrong with that store procedure I set up a trace using SQL server profiler

and used a filter on the textdata column to filter out unwanted T-SQL statements. By looking the duration column in profiler I was able to know how long the store procedure took to execute and it showed that most of the time the store proc took less than 50 milliseconds to finish execution.

In order to monitor the server for a period of time I later configured profiler to store the trace results into a database table. So I did that. However when I later on query the data I found every store proc call recorded took over 10 seconds to execute. I thought I must configure it wrong.

It turns out that the duration shown in SQL server profiler is milliseconds but if you configure profiler to save the result then the duration saved is micro-milliseconds. So if a statement took 50 milliseconds to execute it will show 50 in the duration column of profiler but it will show 50000 in the duration column saved in database table. That is a stupid feature. It confuse user a lot. SQL server 2000 profiler doesn’t have that problem though

Related Posts:

List All Permissions a User Has in SQL Server Database and Error 4064

New Answer on How to Defragment a Table Being Used 24/7

Can Not Connect to SQL Server Named Instance Remotely?

Join to Parametrized Table Function - Cross Apply and Outer Apply in SQL Server 2005

Query to Display Tables with Row Counts