May 1, 2018 at 11:59 am
After a month of upgrading to SQL Server 2016 ENT from SQL Server 2012 ENT we have noticed that SQL Server :Transactions\Transactions have reduced to half. PLE and all other stats shows improvement but can't figure out why this value has reduced to half. Any suggestions will be helpful.
May 1, 2018 at 12:28 pm
No application changes? Do you mean transactions/sec? for a database? or for the instance in total?
May 1, 2018 at 12:49 pm
No application changes. It's the same application, same version. Transactions for instance in total. Both the servers have 16 CPU and SQL Memory capped is same at 146 GB.
May 1, 2018 at 12:55 pm
To give you a brief:
* Our overall user database : Transactions/sec have improved and CPU User time has decreased.
* PLE has almost doubled than SQL 2012 ENT.
* A marginal increase in Tempdb : Trans/sec and Batch requests/sec and SQL Compilations/sec.
* Monthly PLE average has increased to 7531 (SQL 2016) v/s 3675 (SQL 2012).
* Can't figure out why SQL Server Transactions : Transactions have decreased to half. (Perf mon counter)
May 1, 2018 at 1:15 pm
keyurinbox - Tuesday, May 1, 2018 12:55 PMTo give you a brief:* Our overall user database : Transactions/sec have improved and CPU User time has decreased.
* PLE has almost doubled than SQL 2012 ENT.
* A marginal increase in Tempdb : Trans/sec and Batch requests/sec and SQL Compilations/sec.
* Monthly PLE average has increased to 7531 (SQL 2016) v/s 3675 (SQL 2012).
* Can't figure out why SQL Server Transactions : Transactions have decreased to half. (Perf mon counter)
That PerfMon counter is current active transactions. If everything is running faster it seems you would see fewer active transactions at a given point in time if the transaction rate, activity is the same.
Sue
May 1, 2018 at 1:23 pm
If performance has otherwise increased noticeably, this is maybe not so surprising.
That counter is reporting the number of active transactions at the time the the value is sampled, so if transactions are completing more quickly than before, the number of active transactions at most times sampled could be much lower while still having the same number of transactions completing.
Taken to the extreme, if every transaction completed instantaneously, you could potentially have massive transaction/sec throughput while never measuring many/any active transactions any time you measured currently active transactions.
There still might be something else going on, but I thought I'd mention that as one fairly innocent potential explanation, especially given that other metrics seem to have shown improvements.
Cheers!
EDIT: I see after my unintentional long break from SSC my bad habit of starting to write a response, wandering off, and eventually posting without checking for new posts is no better. Sue beat me to it. What she said 🙂
May 2, 2018 at 11:44 am
Thanks a lot guys..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply