SQL Server Profiler: How to inspect/understand the duration for the Audit Logout event in more detail?

Question

I am profiling some MS SQL queries with the SQL Server Profiler for my C# Application that I develop with Visual Studio and IIS Express:

The duration that is given for the event "Audit Logout" (16876 ms) is the total time between login and logout. The duration for the query is only 60 ms.

Login/Logout events are related to the setting up / tearing down.

From What is "Audit Logout" in SQL Server Profiler?

I would like to understand the time difference of 16816 ms (= 16876 ms - 60ms) in more detail.

a) Is it possible to log more events (like a "debug mode")?

b) Is it right to assume that the time difference is only due to tearing down because the end time of the "Audit Login" event is the same as the start time of the query execution?

c) Is there some other tool for analyzing (setup and) tear down times?

d) Does the time difference depend on my query? With other words: would an optimization of the query also help to reduce that time difference?

What I observed so far for @DevTime is that it makes a difference if I start my application the first time (IIS Express is started by Visual Studio, the database is created using the entity framework, example data is written to the database) or if I start it the second time when the database already exists.

For a login after the first start the time difference is about 15 s larger than for a login after the second start. The query that is marked in the example above is executed after user login. Therefore I would expect that the initialization of the database already has been finished at that time and that the initialization would not have any effect on the time difference. Nevertheless it seems to have an influence.

Some related articles:

• What is "Audit Logout" in SQL Server Profiler?

• Fixing slow initial load for IIS

• SQL Server audit logout creates huge number of reads

• https://learn.microsoft.com/de-de/sql/relational-databases/event-classes/audit-logout-event-class

• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84ecfe9e-ff0e-4fc5-962b-cffdcbc619ee/audit-logout-event-seems-slow-on-occasion?forum=sqldatabaseengine

Brocatel

Answer 1

When starting the SQL Profiler a window Trace Properties is shown. The second tab Events Selection is the place where the considered events can be selected.

Activate the option Show all events.

Enable for example the option "Showplan XML FOR Query Compile" under the section "Performance" to log more events.

Also see How to determine what is compiling in SQL Server

Brocatel

Answer 2

I dont have a solution with your recomandations, please the database continue whit problem. all the querys are so good in the process time, only the logout audit constantly show a long time duration. the network its laso good. I think that this logout time affected the efficiency

Saturant