Logins Per Second

SQL Server Administration
1

Hey Guys,

We have experienced some general slowness issues and the only metric I have ever had a bad feeling about is our logins per second. Most of the Articles I find on the internet on the subject are for older versions. We are on SS 2016.

During the day we are seeing average logins at upwards of 1000 to 3000 logins per second. On as system with less then 100 active users.

Anybody know of a way to better monitor what is going on? DMV etc? Or if this is even a problem?

Thanks

Simian

2

what are these logins you talk about?
is your application internal use only or is it exposed to the external www.
Do you have some sort of application using your database? is it a web app or a client app? .net or what?

3

Logins would be the logins to the sql server to perform queries.

The application is internal but uses IIS and is .net C# based.

Logins can be seen with...

select *
from sys.dm_os_performance_counters
WHERE counter_name in ('Logins/sec', 'Logouts/sec')

Thanks

Simi

4

How long has your SQL Server been up and running? These values are cumulative, they are not logins at point of running the query. and you know there are a whole lot more processes that are tapping your database than just your web application. What you will need to do is weed out superfluous logins and focus on logins by the app pool service account?

5

We reboot on Sundays...

Simi

6

really, why?

Anyways, are you doing full backups: bak and logs? Have you identified which artifacts are causing issues?
Do you use linq and entity framework with your c# and/or do you use stored procedures?

7

really, why?
Slowness issues with the application get worse over time. Programmers wanted a weekly reboot. All indications are the SQL Server is fine, I don't think it needs a reboot but they want it.

Anyways, are you doing full backups: bak and logs?
Yes, full backups

Have you identified which artifacts are causing issues?
No, I can't find any problem on the SQL Server side, I am looking for any clues.

Do you use linq and entity framework with your c# and/or do you use stored procedures?
Not sure about linq and entity framework but we do use stored procedures.

Thanks

Simi

8

You will need to do some forensic work on your sql server and the reasons why and what is causing slowness

  1. Identify slow running queries
    https://www.red-gate.com/simple-talk/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/
  2. Are there any other type of applications, data reads for reports, queries by users using query tools and bypassing application accessing the data? OLAP vs OLTP
9

We really don't have any slow queries... We only have about 150 queries a day that exceed 600 ms.

I am sure most of our problems lie with our application but I am trying to prove that...

Thanks

Simi

10

Yesterday I created an extended event and monitored logins. I recieved about 30,000 in 6 or 7 minutes...

Simi

11

The trick is to know whether they are pool or non-pooled connections. If they are pooled connections that's fine. If they are non-pooled then that can be an issue.

You can run Profiler for the Login Event and look at the event subclass. That will tell you pooled vs. non-pooled. Profiler will also tell you which applications are doing the logins.

A pooled login is a very lightweight way to reuse a SQL Server connection.

With that many connections I'd only run it for a minute or two outside of business hours.

12

That sounds like possible "connection leaks" and "memory leaks" from software that doesn't actually close out what it uses. Also, you need to have something that updates statistics properly.

13

Hey Jeff,

Always good to hear from you…

So is the Logins/sec counter even a useful metric anymore?

SELECT

14

Yowch... 30,000 logins in 6 or 7 minutes. That's pretty hefty. The Logins/sec counter might be an indication of something weird like establishing a connection, getting one piece of information, closing the connection, wash-rinse-repeat, for every object on multiple screens. You'd have to look at the actual calls on the database to see what's happening there.

As for "slow" queries... the slowest queries are actually usually pretty fast. It's when there are thousands of nearly identical queries that take far less than 600 milliseconds. The real killer with those is if they have to compile for each slight change. We had such a thing going on... < 200 ms to execute but 2 to 22 SECONDS to compile for each and every run. Of course, that also made proc cache go absolutely nuts. We moved the query from being EF generated to a stored procedure and optimized it. We got the execution time down to < 10ms and was totally reusable so virtually no compile time except on the first run and that was certainly < 2 Seconds.

1 Like
15

ef can be a killer in many instances!!!