I have started to collect queries to analyze the memory and cpu usage on our server.
My understanding of sql is quite basic and many information on the web are apparently outdated.
Microsoft learn is ok for most of the troubleshooting I need, but I started to realize our server is probably underused or not fine tuned and I would like to learn more.
We have a dual cpu with 16 cores @2GHz and 96GB of RAM
SQL is set to take about 80GB of memory as max and in a week it comes close to that, our db is 12 GB so it goes in memory and the rest is mainly tempdb stuff apparently.
From a query I found on the web the cpu had run like 60k single threaded “tasks” (I do not recall the correct word).
I monitor the SQL over RDS and with task manager opened I rarely see multiple cores working in parallel. Our parallelization cost is set at 5 the mssql default, many suggest a good spot at 20-40 depending on the workload, but my understanding is that increasing the value will make parallelization more difficult.
While I understand that parallelization is not always faster, I would like to benchmark some real PDM scenario and look at the data to compare settings and make a correct judgment.
Any idea?