Implausible values of logins and transactions per sec

1

Hey everyone,

I set up telegraf on my test machine. There are only 3 SQL users. Nevertheless telegraf counts extremely high transactions per second. So many transactions per second my VM would not be able to handle.

According to statistics, TempDB generates more than 1.5 million transactions in one second? Surely that can never be true? A Instance, with 9 Users generates more than 500 Thousends Logins PER SECOUND?

{"fields":{"Logins/sec":527683,"Logouts/sec":527674,"User Connections":9},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"                                                                                                                                ","object":"MSSQL$INSTANCE01:General Statistics","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":1587448},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"tempdb                                                                                                                          ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":44362},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"MAX                                                                                                                          ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":26312},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"Nordwind                                                                                                                        ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":73480},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"mssqlsystemresource                                                                                                             ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":67330},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"msdb                                                                                                                            ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":65348},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"model                                                                                                                           ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":1161684},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"master                                                                                                                          ","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
{"fields":{"Transactions/sec":3025966},"name":"sqlserver_performance","tags":{"database_name":"master","host":"MSSQL-SA","instance":"Total","object":"MSSQL$INSTANCE01:Databases","sql_instance":"MSSQL-SA:INSTANCE01"},"timestamp":1584967741}
2

This is not a Telegraf problem. if you query SQL server directly you will get the same values, therefore I suggest you have a look about what can cause this on SQL Server.

I’ve found this:

Do logouts/sec match logins/sec? If so you should have a look at profiler and ensure that you are actually utilizing connection pooling. Grab the audit login, logout events as well as RPC:Completed and look for calls to sp_reset_connection - that will indicate at least some pooling is going on.
Should you see only login and logout events with no sp_reset_connections then the app isn’t using pooling, and you’ll need to track down why.

But probably there are other things that can cause that

1 Like