April 12, 2010 at 2:50 pm
I am trying to find out what causes a record with an EventClass of "Audit Login" and another of "Audit Logout" when I am performing a trace using the TSQL_Replay template on SQL 2005.
I am trying to troubleshoot a page load time that is about 2 seconds after SQL Server 2005 has been restarted but after a day or two of the same performace, it suddently goes up to a page load time of about 10 seconds. While performing a trace we have noticed that the duration of these "Audit Login" and "Audit Logout" events seem to go substantially up (when the page load time is slow) and they roughly add up to the difference in page load times.
So I first want to see what causes these events to occur and then once I understand that, I want to find out what could cause the time taken for these events to increase. I am not sure if these are causing the problem or not, but we are trying to eliminate possibilities.
As a bit more information on the above, a section in the webpage is doing a loop and calling the same stored procedure with different parameters each time it loops. In the trace it has a "Audit Login" record, then a "SQL:BatchStarting", "SQL:BatchCompleted" and then followed by a "Audit Lgout". Other places in the webpage have more than one sql command sandwiched between the audit login and logout.
Thanks in advance.
April 15, 2010 at 8:52 am
Does anyone have an idea on what could cause the time length taken for the Audit Login and Audit Logout records to increase?
May 10, 2010 at 7:30 am
The duration for the audit logout event is the time passed since the login connected:
http://msdn.microsoft.com/en-us/library/ms175827(SQL.90).aspx
The results you see are as expected. Does the result from the stored proc vary in size substantially? Have you tried using the "with recompile" option on the proc?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply