Based on the waits statistics in your system you have incorrect settings for parallelism.
Maxdop in your system should not be more than 16, you can find the correct value based on this MS article: Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server
Here are some T-SQL scripts to do it (I don't know your ...
Is there any good practice or specific reason why SQL Server should have the same memory configuration across different replicas?
This is definitely not a rule or prerequisite, normally people tend to keep configuration same so that when failover happens and secondary becomes primary should should not face resource crunch and performance should not get ...
That’s due to fact it (SECONDARY) has to serve same level of work load as PRIMARY. I.e. when PRIMARY is memory intensive for 32GB, and SECONDARY limits to 24GB in the incident when it’s become PRIMARY it could perform same as primary the impact (8GB Memory) could be in various factors
Additional connections might not established
Existing requests might be ...
we are not using the mulitdimensional model, not tabular
Do you mean NOT multi-dimensional ONLY TABULAR?
It's not best practice to install SSAS and SQL Server together on one server as the SSAS (Tabular) VertiPaq Engine works totally different than SQL Engine and it's (VertiPaq) is more memory intensive unless the models configured as ...
SQL will always use up the RAM it has to cache data.
Setting a min/max RAM for your instance is always a good idea (especially MAX, so you keep some RAM for your OS)
See this good answer on dba.SE:Why is SQL Server consuming more server memory?
Right click your instance in SSMS -> Memory -> Fill in min/max memory. It's a good idea to leave at least 4 GB for ...
You can set Maxsize on both the data and the log files of your database.
If the Max size of your database is 20GB you have to make sure the combined max size of both is 20 GB.
Remember to allocate enough room for the log file, so you don't run into issues early on.
You can do this by going into SSMS, right click your database, properties and go to Files.
Report Server executes the query and stores a copy of it within its cache. This is outside of the SQL Server process and so you will need to account for it when architecting your installation. Generally, the biggest winner I have noticed when I have done installations myself is the number of CPU's. RAM helps of course, but I have found additional CPU's to ...
max server memory is the maximum you will allow SQL to use. This is primarily set to leave memory for other process like the operating system, or if you have multiple instances on one machine.
Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the ...
run this to check configuration:
sp_configure 'max server memory (mb)'
sp_configure 'min server memory (MB)'
if you are still concerned it won't access the memory up the minimum to 8GB, and see if the values changed accordingly:
sp_configure 'min server memory (MB)', 8192
reconfigure with override
set it back to the value observed in the first call to ...