Menu
I'm running a data import (using C#/Linq), and naturally I'm trying to optimize my queries as much as possible. To this end I'm running a trace on the DB using SQL Server Profiler, with my trace filtered by my SQL login name (it's a name that can uniquely be attributed to my data import process).
Strangely enough, most of my SQL statements are really quick :) - very few queries even break over the 1ms mark. But spaced in between all my queries are several rows where the EventClass is "Audit Login" or "Audit Logout" - and the duration of an "Audit Logout" can be up to a minute!
Has this got something to do with the fact that I'm using transactions in my import? If so, is there any way to find which are the big-hitting queries so I can clean those up?
531
Enabling SQL login auditing will help you detect insider and outsider threats in time to protect your SQL databases against data breaches. These capabilities are included in SQL Server audit services, but this is not a very convenient solution because truly suspicious events get buried in the huge amount of noise data.
One of the options to audit failed logins is to turn on the appropriate option in SQL Server in the Server Properties dialog (the Security node) of a SQL Server instance in Object Explorer. The setting is on the instance level. The SQL Server instance must be running to capture failed logins.
If I remember correct, the duration of an Audit Logout is the amount of time the connection was open. E.g. nothing to do with speed of the command - just the amount of time the login was 'logged in'.
139
Login/Logout events are related to the setting up / tearing down. IIRC the time is the 'was logged in for time' as opposed to a processing duration as with other log events.
In general, one hides these events unless you suspect there's an issue with connection pool management etc.
The raw times for the batches should be sufficient to diagnose the time the actual activity is taking including the impact of any transactions etc.
12
The Audit Logout event class indicates that a user has logged out of (logged off) Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.
it's the total time the connection was logged in for, including idle time, so it doesn't indicate a performance problem. Also profiling logins/logouts is very unlikely to cause a performance problem. You'd be better off looking for poorly performing queries, possibly long-running queries.
For more info i suggest https://msdn.microsoft.com/en-us/library/ms175827.aspx :)
5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
