Varying Event Durations Can Point to Compilation Problems

When I use SQL Server Profiler to track performance problems, I sometimes notice large differences—as much as 2 or 3 seconds—between the duration for the SQL:BatchCompleted event and the SQL:StmtCompleted event. Why are the duration times different?

Generally speaking, the difference between a SQL:BatchCompleted event's duration time and a SQL:StmtCompleted event's duration time is how long each event takes to parse and compile a query. SQL Server can usually parse and compile a batch quickly. However, sometimes a batch is complicated or other stresses on the system cause the compilation time to become a significant part of the time it takes SQL Server to run a query. So, large differences between the duration of SQL:BatchCompleted and SQL:StmtCompleted events can point to queries that have compilation problems.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.