To track the login statistical information, enable thesp_configure value ‘common criteria compliance enabled‘ setting and restart SQL Server.
The sys.dm_exec_sessions DMV is one way to query the statistical login information.
The rows shown in this example belong to the sa user on my system. The values are different because the values are obtained when the specific session was created (at login time for the individual session.)
Session 57 logged in at 15:49:54.383 and the previous, sa, successful login took place at 15:49:54.360
Where-as Session 67 logged in at 15:51:02.410 and the previous, sa, successful login took place at 15:49:55.413
Note:The first time you login for a newly created user the last* (previous) values areNULLindicating that the user had not previously logged onto to the system.
The information flow:
Client sends login request to SQL Server
SQL Server creates new session object
SQL Server acquires lock for the LOGIN_STATS associated with the login user
Save the current row information (last values) with the new session object
Update the LOGIN_STATS row with the current time for successful login
LOGIN_STATS (Lock Type By Stat Row) – Partitioned
It just so happens that I was testing on a 2TB, 132 CPU system and encountered heavy blocking on the same lock for LOGIN_STATS (login id). Investigating the blocking I found the last login values are stored in a system table, in the master database and protected by the LOGIN_STATS (login id) lock.
Warning:Make sure you are backing up the master database properly to avoid log growth and loss of common criteria information.
The test I was running used thesame login, with hundreds of connections doing connect and disconnect operations with common criteria enabled. The same login pattern leads to the same LOGIN_STATS(login id) lock becoming a bottleneck. Each login is updating the last* information using the appropriate LOGIN_STATS, SCH_M lock. Instead, if the test used different users for the connections each connection would have obtained the SCH_M lock for the specific login and avoided the contention.
To make the investigation a bit more interesting the LOGIN_STATS lock type is one of a handful of lock types that is partitioned by the SQL Server Engine.
The 132 CPU system results in 132 possible lock partitions. Obtaining a shared (SH) lock on the session is a local partition only operation. However, the lock type requested is SCH_M because the last* values were being updated. To acquire the SCH_M the SCH_M must be granted in all 132 partitions, in order from 0 to 131. As you can envision the more CPUs the more partitions. The more partitions the longer it takes to acquire the SCH_M lock. The longer it takes for a single session to acquire the SCH_M then perform changes to the last* information and release the lock the longer the convey lasts/can become.
The solution:Turn off the LOGIN_STATS tracking (disable common criteria) or use a different login user for the various connections in the test.