August 7, 2017 at 9:19 am
Hi guys
Just after someone checking a few server settings for me to see if I've done anything dim (it's entirely possible).
We have 2 servers that seem to be running fine (apart from SSRS), I just wondered if I could get any better performance with "quick wins".
SSRS seems to run very slowly (and a lot slower than our older server with approximately half the resources.
Details of the servers are:-
Server 1
16GB RAM - 12GB available to SQL
8 CPUs (1 NUMA node) - Max degree of parallelism set to 4
I've set Cost Threshold for parallelism to 75
This server also has SSAS installed with some RAM ring-fenced for that.
Server 2
32GB RAM - 16GB available to SQL
8 CPUs (1 NUMA node) - Max degree of parallelism set to 4
I've set Cost Threshold for parallelism to 75
This server also has SSAS installed with some RAM ring-fenced for that.
Can anyone see anything ridiculously wrong with this lot?
I can post other settings if necessary.
Thanks in advance....
August 7, 2017 at 9:23 am
12GB isn't really a lot for a SQL Server. Especially if you have SSAS, SSIS and SSRS all on the same instance. YOu also say you have RAM ring fenced for SSAS, Is this in addition to the remaining 4GB? If not SSAS is really going to struggle as it needs to share those 4GB with SSRS, SSIS, the OS (probably going to be using at least 2GB), and anything else running on the server. Is the Server not expected to be doing much most of the time? Is slow calculation time acceptable?
Out of interest, what licence are you running, Standard, BI or Enterprise?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 7, 2017 at 9:29 am
Hi Thom
On Server1 there is a total of 16GB.
I've set up SQL to be able to access 12GB of that (leaving 4GB for Windows as I understand it).
It's my understanding that SSAS will take a proportion of the 12GB for it's own use.
This server is using the Developer edition of SQL2012 (we're upgrading the SQL tomorrow to 2016 Developer).
::edit::
Not sure if it makes any difference but both of these servers as virtual machines so we can ask for more RAM if necessary.
August 7, 2017 at 9:39 am
richardmgreen1 - Monday, August 7, 2017 9:29 AMIt's my understanding that SSAS will take a proportion of the 12GB for it's own use.
Wrong. SSAS, SSRS, SSIS and SQL Server (Data Engine) are all separate services and use their own resources. Limiting the data engine will 12GB, on a 16GB server, will leave 4GB any other processes. That will include SSAS, SSRS, and SSIS.
As an example, on SQL Server Standard edition each service is limited to 64GB. This means that (potentially) SQL Server, SSAS, SSIS, and SSRS could each use 64GB of RAM at the same time, totalling 256GB. That's before you take into account OS resources.
On your server1, therefore, that 4GB will need to be shared between SSAS, SSRS, SSIS (if you're using it) and the OS. Considering that, like I said, the OS is probably using about 2GB, that leaves the other services with only 2GB to play with between the 3 of them. That, more than likely, won't play well.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 8, 2017 at 1:45 am
Thanks for that. Looks like I mis-read something then.
I'll request more RAM for the server(s) and do some re-configuring and see if we can get them running any better.
August 13, 2017 at 5:10 am
SSIS,SSAS, SSRS work under OS services separate from the main SQL server service. Hence use separate memory area i.e from what left over (from Total - (SQL server memory + O.S ), assuming no other apps use that server).
I would also suggest to gather memory stats for these services at different times of the day to understand the memory requirements for these apps to run better .
Arshad
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply