SolarWinds SQL Peformance Critical Counters

cdt_mueller

Can some of you share your SQL performance metrics?

I am seeing the following and they are consistence but I am reticent to make them baseline without some comparison:

Page Writes/sec

Current Value - 486.95

Recommended – 80-90

Page Splits/sec

Current Value - 23.44

Recommended – As low as possible

Workfiles Created/sec

Current Value - 249.78

Recommended - < 20

Worktables Created/sec

Current Value - 67.07

Recommended – < 20

Appreciate any contributions : )

Find more posts tagged with

sam

Performance

orion

sql

Accepted answers

jm_sysadmin

So here they are cdt_mueller, but remember that SQL performance will vary widely from one install to the next based on the modules you have and the amount you have in the system. So I am posting the values so you can see them, but I am not sure how much they will help. I included other stuff to help you decide.
I can say that right now my SQL performance feels fine. We have had slowness issues in the past, and this is the performance I consider normal, and people don't complain. Things will get a little faster over the winter as we move to faster CPU's, and possibly increase memory.

Page Writes/sec

Average 2,400 Peak 6100

Recommended baseline from AppInsight – 3437 - 4136

Page Splits/sec

Average 105 Peak 980

Recommended baseline from AppInsight – 705 - 959

Workfiles Created/sec

Average 733 Peak 1000

Recommended baseline from AppInsight - 1125 - 1334

Worktables Created/sec

Average 210 Peak 250

Recommended baseline from AppInsight – 298 - 350

Here's what I have installed:

Orion Platform 2019.2 HF1, SRM 6.9.0, IPAM 4.9.0, SAM 6.9.1, SCM 1.2.0 HF1, DPAIM 11.2.0, NCM 8.0, VMAN 8.5.0 HF1, Toolset 11.0.8, PM 2.1.7, NetPath 1.1.5, NPM 12.5, NTA 4.6.0
Size of install from the Database details page:
Database size 255700 MB

Network Elements	27612
Nodes	4716
Interfaces	15015
Volumes	7881

Events	1700644
Pollers	119215

Since disk performance for Orion has been the big performance factor, I pulled that too:

We also have DPA in house, so maybe these will also be helpful. Its just from today.

The blocks below are mostly from the Patch manager integration, but the transactions should give a decent look into how much work it has.

All comments

serena

cdt_mueller wrote:
Can some of you share your SQL performance metrics?
I am seeing the following and they are consistence but I am reticent to make them baseline without some comparison:
Page Writes/sec
Current Value - 486.95
Recommended – 80-90

Page Splits/sec
Current Value - 23.44
Recommended – As low as possible

Workfiles Created/sec
Current Value - 249.78
Recommended - < 20

Worktables Created/sec
Current Value - 67.07
Recommended – < 20
Appreciate any contributions : )

I'm curious, are you looking for SQL baselines for your Orion SQL server?

cdt_mueller

Yes, sorry should have mentioned that : ) This is our Orion backend SQL server.

mesverrum

This is for my lab environment, it holds multiple copies of small instances of Orion, ARM, patch manager, and WHD, so its a little messy, but none of these baby environments has more than maybe 50 nodes in them so it's not a great example of a stress test, just a datapoint for a small system.

Page Writes/sec

Current Value - 8.39

Page Splits/sec

Current Value - 1.52

Workfiles Created/sec

Current Value - 32.43

Worktables Created/sec

Current Value - 8.66

cdt_mueller

Thanks : )

Jeff Mueller

SQL Server Support

Department of Technology

916.431.5843

cdt_mueller

Thanks!

Jeff Mueller

SQL Server Support

Department of Technology

916.431.5843

jm_sysadmin

Page Writes/sec

Average 2,400 Peak 6100

Recommended baseline from AppInsight – 3437 - 4136

Page Splits/sec

Average 105 Peak 980

Recommended baseline from AppInsight – 705 - 959

Workfiles Created/sec

Average 733 Peak 1000

Recommended baseline from AppInsight - 1125 - 1334

Worktables Created/sec

Average 210 Peak 250

Recommended baseline from AppInsight – 298 - 350

Here's what I have installed:

Network Elements	27612
Nodes	4716
Interfaces	15015
Volumes	7881

Events	1700644
Pollers	119215

Since disk performance for Orion has been the big performance factor, I pulled that too:

We also have DPA in house, so maybe these will also be helpful. Its just from today.

The blocks below are mostly from the Patch manager integration, but the transactions should give a decent look into how much work it has.

cdt_mueller

OMG! Fantastic : D Thanks so much for all that effort! I should have been more specific in that I have a large site (like yours) so I am thankful you provided input. Looks like I have nothing to worry about : ) I will get the same information you provided from our SolarWinds staff (I'm just a SAM admin) so other Thwackers can see the comparison too. This is so helpful for us. Thanks again.

jm_sysadmin

I remember setting up the SQL AppInsight for the Orion database with the default values very shortly after I installed SAM, and it lit up like Christmas. I figured you might want a bit more information.

cdt_mueller

Mine lit up too! None of our other 200+ SQL servers have stats like this. We are still adjusting CPU (on the pollers and web) and memory cap (SQL) to compensate for the load. We have Orion 18.2 and recently we had an agent memory leak which really messes with stuff!

cdt_mueller

Here is a compare with our environment:

Orion Platform 2018.2 HF6, SAM 6.7.0, DPAIM 11.1.0, NCM 7.8, Toolset 11.0.6, NetPath 1.1.3, NPM 12.3, NTA 4.4.0, WPM 2.2.2
SQL DB	255700 MB	94882 MB
Network Elements	27612	55567
Nodes	4716	3492
Interfaces	15015	41225
Volumes	7881	10850
Events	1700644	369154
Pollers	119215	213038